title: Excel VBA Debugging
body: <p>I'm running into a &quot;run time error 1004&quot;.  I suspect this has something to do with how much data I want my code to process.  Currently I am running a 246 column by 30,000 row.  What I'm trying to achieve is to consolidate my data into one row item because the current system export the data into individual row as a duplicate for certain data columns.  As a result, the data has a ladder/stagger effect where there's duplicate row ID with blank cells in one and data below it.</p>
  <p>Example:</p>
  <p><a href="https://i.stack.imgur.com/ahW5l.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/ahW5l.png" alt="enter image description here" /></a></p>
  <p>Code:</p>
  <pre><code>Option Explicit
  
  Sub consolidate()
  
      Const SHEET_NAME = &quot;Archer Search Report&quot;
      Const NO_OF_COLS = 101
  
      Dim wb As Workbook, ws As Worksheet
      Dim irow As Long, iLastRow As Long, c As Long, count As Long
  
      Set wb = ThisWorkbook
      Set ws = wb.Sheets(SHEET_NAME)
      iLastRow = ws.Range(&quot;A&quot; &amp; Rows.count).End(xlUp).Row
  
      ' scan up sheet
      For irow = iLastRow - 1 To 2 Step -1
  
           ' if same id below
          If ws.Cells(irow + 1, 1) = ws.Cells(irow, 1) Then
  
              ' scan across
              For c = 1 To NO_OF_COLS
                  ' if blank copy from below
                  If Len(ws.Cells(irow, c)) = 0 Then
                     ws.Cells(irow, c) = ws.Cells(irow + 1, c)
                  End If
              Next
  
              ws.Rows(irow + 1).Delete
              count = count + 1
  
          End If
  
      Next
  
      MsgBox iLastRow - 1 &amp; &quot; rows scanned&quot; &amp; vbCr &amp; _
             count &amp; &quot; rows deleted from &quot; &amp; ws.Name, vbInformation
  
  End Sub
  </code></pre>
  <p>I suspect it has to do with the massive amount of data it's running and wanted to see if that is the case.  If so, is there an alternative approach?  Appreciate the assistance.</p>
  <p>Note: I got this awesome code from someone(CDP1802)here and have been using it for years with smaller data set.</p>