I have a workbook that I use to reformat data from its original format into the format I want.
In that workbook there is a macro that deletes all of the blank rows on the main sheet
Code:
Sub delblankrows()
Dim s1 As Worksheet
Dim tmpR As Range
Dim rowcount As Long, colcount As Long, i As Long, j As Long, k As Boolean
'Change "Sheet1" to the name of your worksheet.
Set s1 = Sheets("sheet1")
Set tmpR = s1.UsedRange
rowcount = tmpR.Rows.Count
colcount = tmpR.Columns.Count
'Starts from bottom row and looks for non-empty cells from left to right.
'Moves to row above if non-empty cell is found.
'If none is found, then deletes row and shifts values up.
For i = rowcount To 1 Step -1
k = 0
For j = 1 To colcount
If tmpR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
I have used this line of code in numerous other sheets and it works fine. In this book, however, there are rows that appear empty but when I run the macro those do not get deleted.
When I select the row and "clear contents" and run the macro again the row gets deleted.
I dont even know where to start figuring this one out....