Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save danwagnerco/d15a5ed0f6d1970a8d90 to your computer and use it in GitHub Desktop.
Save danwagnerco/d15a5ed0f6d1970a8d90 to your computer and use it in GitHub Desktop.
This script examines a dynamic range and deletes empty rows (based on the range size)
Option Explicit
Public Sub DeleteBlankRows()
Dim wks As Worksheet
Dim lngLastRow As Long, lngLastCol As Long, lngIdx As Long, _
lngColCounter As Long
Dim blnAllBlank As Boolean
'First things first: we identify our basic variables
Set wks = ThisWorkbook.Worksheets("hello")
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
'Awesome! With the last row and column in our pocket, we can
'loop over the full range (whether it only has 2 columns or 4)
'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
'That's it -- we're done!
MsgBox "Script complete!"
End Sub
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment