MACRO HELP - deleting rows containing a range of blank cells
If you want to check a range on that row:
Option Explicit
Sub DeleteEmptyRows()
Dim LastRow As Long
Dim r As Long
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Cells(r, "A").Resize(1, 13)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub
This checks for empty cells in A:M in the used range--not formulas that evaluate
to "" or those formulas that have been converted to values.
This line:
Cells(r, "A").Resize(1, 13)
Is the cell in A1, but resized to a single row (1) and 13 columns (A:M).
DavidHawes wrote:
Hi,
Thanks for this, although it's not quite what i'm after and I can't quite
seem to make the code work using the second example you quote...
What I require is for a whole row to be deleted if the cells in specified
rows within my table (in my case A4:M4, A5:M5, A6:M6 etc) are blank.
I tried the following using your example:
On Error Resume Next
Range("A10:M11").Cells.SpecialCells(xlCellTypeBlan ks) _
.EntireRow.Delete
On Error GoTo 0
but this didn't work.
Any help would be gratefully appreciated.
Many thanks,
David Hawes
"Dave Peterson" wrote:
Option Explicit
Sub DeleteEmptyRowsInSelectionIfWholeRowIsEmpty()
Dim FirstRow As Long
Dim LastRow As Long
Dim r As Long
Dim myRng As Range
Set myRng = Selection.Areas(1).EntireRow.Columns(1)
With myRng
FirstRow = .Row
LastRow = .Rows(.Rows.Count).Row
End With
Application.ScreenUpdating = False
For r = LastRow To FirstRow Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub
Based on one cell:
Option Explicit
Sub DeleteEmptyRowsInSelectionIfCellIsEmpty()
On Error Resume Next
Selection.Areas(1).Columns(1).Cells.SpecialCells(x lCellTypeBlanks) _
.EntireRow.Delete
On Error GoTo 0
End Sub
DavidHawes wrote:
Just wanted to move this up the forums as my subsequent question (below)
would be lost otherwise...
---------------------
As an extra, if I wanted to specify a range of cells that, if blank, would
result in the deletion of the rows where those cells lie, how would I do this?
Thanks again.
David
"Gord Dibben" wrote:
David
Try this macro to delete just empty rows.
Sub DeleteEmptyRows()
''only if entire row is blank
LastRow = ActiveSheet.UsedRange.Row - 1 + _
ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.CountA(Rows(r)) = 0 Then
Rows(r).Delete
End If
Next r
End Sub
Gord Dibben MS Excel MVP
On Fri, 23 Feb 2007 05:33:29 -0800, DavidHawes
wrote:
Hi,
I've set up a macro which re-organises an excel spreadsheet into a format
that enables me to import the data contained within it directly into an
Access database i've set up.
This works perfectly.
However, I want the macro to delete out any lines of my table (which is
fixed in size) that do not contain any data.
Is this possible? If so, can someone please explain what code I need to
enter to get my macro to do this?
Many thanks,
David :-)
--
Dave Peterson
--
Dave Peterson
|