I have the following code that checks a selected spreadsheet for blank rows of data before it does any manipulation of the cells and then importing the data into my table in access. It works great except for the very first line in the spreadsheet that has merged cells in columns A-I. It keeps deleting this row. I am not understanding where to modify my code to get it to stop this. It took me all day to get this far. Thank you for your help in advance.
-Walker
Code:
Public Sub DeleteBlankRows(xls As Object) 'xls is active worksheet being modified from other subroutine
Dim wks As Worksheet
Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
lngColCounter As Long
Dim blnAllBlank As Boolean
Set wks = xls
With wks
'Now that our sheet is defined, we'll find the last row and last column
lngLastRow = .Cells.Find(What:="*", LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastCol = .Cells.Find(What:="*", LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
'Since we need to delete rows, we start from the bottom and move up
For lngIdx = lngLastRow To 1 Step -1
'Start by setting a flag to immediately stop checking
'if a cell is NOT blank and initializing the column counter
blnAllBlank = True
lngColCounter = 2
'Check cells from left to right while the flag is True
'and the we are within the farthest-right column
While blnAllBlank And lngColCounter <= lngLastCol
'If the cell is NOT blank, trip the flag and exit the loop
If .Cells(lngIdx, lngColCounter) <> "" Then
blnAllBlank = False
Else
lngColCounter = lngColCounter + 1
End If
Wend
'Delete the row if the blnBlank variable is True
If blnAllBlank Then
.Rows(lngIdx).Delete
End If
Next lngIdx
End With
'MsgBox "Blank rows have been deleted."
End Sub