none
How many bytes per row of data in Excel RRS feed

  • Question

  • Hi folks,

    I have million rows of data and I'm trying to size each row that comes with 30 columns which are a mix of numerical and text characters. Is there a formula anyone can share that I can use in my Excel file to calculate the total byte size of each row so I can add them up for the million rows to see what am I dealing with? Thanks so much!

    Sunday, March 8, 2020 12:47 AM

All replies

  • Hi GoMSACCESS,

    I've made a macro (not Formula in cells).
        

    [VBA code]
    Sub Count_Bytes()
        Dim totalBytes As Long: totalBytes = 0
        Dim lastRow As Long
        ' --- assuming there is no blank cell before the last cell in column A
        lastRow = Cells(Rows.Count, 1).End(xlUp).Row
        ' ---
        Dim r, c As Long
        For r = 1 To lastRow
            For c = 1 To 30
                totalBytes = totalBytes + Len(Cells(r, c).Value)
            Next
            ' ---
            MsgBox "Row = " & r & " -- total bytes = " & totalBytes
        Next
    End Sub
    If you want to put the total bytes of each row instead of using MsgBox, please put them in another sheet. 

    Regards,

    Ashidacchi -- http://hokusosha.com

    • Proposed as answer by Ashidacchi Thursday, March 12, 2020 8:10 PM
    Monday, March 9, 2020 3:58 AM
  • Hi,

    This forum is for discussion the windows desktop development.

    But this thread is more related to Excel develop, you could ask here:

    https://social.msdn.microsoft.com/Forums/Lync/en-US/home?forum=exceldev

    Best Regards,

    Drake


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 9, 2020 9:35 AM
  • Thank you. That answered my questions.
    Thursday, March 12, 2020 8:20 PM