Delete the last blank rows.

  • Thread starter Thread starter mike
  • Start date Start date
M

mike

This macro is fast in deleting last blank rows. What if i
want it to delete all last blank rows. Since i have macro
that will auto fliter the blanks but the blank rows might
varies.Thanks in advance.
set rng = cells(rows.count,1).End(xlup)

rng.offset(-21,0).Resize(22).Entirerow.delete
 
Assuming the blank rows are the only ones in the Autofilter that are
visible.
set rng = ActiveSheet.Autofilter.Range
' exclude the header row
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
rng.EntireRow.specialCells(xlvisible).Entirerow.Delete


However, blank rows can limit the extent of the autofilter range, so this
assumes they are included in that range (they were part of the selection
when the autofilter was applied.)
 
Back
Top