Scott, the code from Gord and Mike work for me, here is another way
Sub Delete_blank_Column1()
On Error Resume Next
Intersect(Range("A:A"), ActiveSheet.UsedRange) _
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub
--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003
"Scott" <(E-Mail Removed)> wrote in message
news:O0%23CY%(E-Mail Removed)...
> for some reason, the code still isn't deleting my row with empty cell in
> column 1. Can you check it again?
>
> "Mike Fogleman" <(E-Mail Removed)> wrote in message
> news:3OCdnU9t4psH-(E-Mail Removed)...
> > You need to find the last row as a variable:
> >
> > Sub DeleteRowsWithEmptyColumn1()
> > ' deletes rows with empty cells in column 1
> > Dim rng As Range
> > Dim LRow As Long
> >
> > On Error Resume Next
> > LRow = Cells(Rows.Count, 1).End(xlUp).Row
> > Set rng = ActiveSheet.Range("A1:A" & LRow).SpecialCells(xlBlanks)
> > On Error GoTo 0
> > If Not rng Is Nothing Then
> > rng.EntireRow.Delete
> > End If
> > End Sub
> >
> > Mike F
> > "Scott" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >>I get an error "Cannot use that command on overlapping selections". It
> >>doesn't like " rng.EntireRow.Delete"
> >>
> >>
> >> "Gord Dibben" <gorddibbATshawDOTca> wrote in message
> >> news:(E-Mail Removed)...
> >>> Sub DeleteRowsWithEmptyColumn1()
> >>> ' deletes rows with empty cells in column 1
> >>> Dim rng As Range
> >>> On Error Resume Next
> >>> Set rng = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) _
> >>> .SpecialCells(xlBlanks)
> >>> On Error GoTo 0
> >>> If Not rng Is Nothing Then
> >>> rng.EntireRow.Delete
> >>> End If
> >>> End Sub
> >>>
> >>>
> >>> Gord Dibben MS Excel MVP
> >>>
> >>> On Fri, 29 Sep 2006 19:39:09 -0500, "Scott" <(E-Mail Removed)>
> >>> wrote:
> >>>
> >>>>My below sub below corectly deletes any rows that have empty cells
> >>>>within a
> >>>>hard-coded range of A1:A150. What I'd like to do is to search the
entire
> >>>>range of cells containing values and delete any rows that have empty
> >>>>column
> >>>>1 cells.
> >>>>
> >>>>It would achieve the same reults as my current code, but I could get
rid
> >>>>of
> >>>>my hard coded reference. I never will know how many rows my
spreadsheet
> >>>>will
> >>>>be and would like to avoid hard-code reference.
> >>>>
> >>>>Can someone help me re-write my code to achieve this?
> >>>>
> >>>>
> >>>>CODE **********************
> >>>>
> >>>>Sub DeleteRowsWithEmptyColumn1()
> >>>> ' deletes rows with empty cells in column 1
> >>>> Dim rng As Range
> >>>> On Error Resume Next
> >>>> Set rng = ActiveSheet.Range("A1:A150").SpecialCells(xlBlanks)
> >>>> On Error GoTo 0
> >>>> If Not rng Is Nothing Then
> >>>> rng.EntireRow.Delete
> >>>> End If
> >>>>End Sub
> >>>>
> >>>
> >>
> >>
> >
> >
>
>
|
Similar Threads
