Okay, try this code instead...
Dim FirstBlankCell As Range, LastRow As Long
'....
'....
On Error Resume Next
LastRow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row
Set FirstBlankCell = Worksheets("Sheet1").Range("A:J"). _
SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
If Not FirstBlankCell Is Nothing Then
Rows(FirstBlankCell(1).Row & ":" & LastRow).ClearContents
End If
--
Rick (MVP - Excel)
"Danielle" <(E-Mail Removed)> wrote in message
news:A7E7F034-66CD-45A2-BD28-(E-Mail Removed)...
> Thanks Rick,
>
> This works except that the range on the spreadsheet is different with each
> report so it doesn't always end on row 100. I need it to find where it
> ends,
> then do the delete.
>
> Any other thoughts?
>
> "Rick Rothstein" wrote:
>
>> Perhaps this code will do what you want...
>>
>> Dim FirstBlankCell As Range
>> '....
>> '....
>> On Error Resume Next
>> Set FirstBlankCell = Worksheets("Sheet1").Range("A1:J100"). _
>> SpecialCells(xlCellTypeBlanks)
>> On Error GoTo 0
>> If Not FirstBlankCell Is Nothing Then
>> Rows(FirstBlankCell(1).Row & ":100").ClearContents
>> End If
>>
>> Note 1: Since you cannot Undo the deletions from a macro, you should
>> first test this code out on a copy of your worksheet and not
>> on your live data.
>>
>> Note 2: Change my reference to Worksheets("Sheet1") to reflect the
>> actual worksheet name you want to apply this code to.
>>
>> --
>> Rick (MVP - Excel)
>>
>>
>> "Danielle" <(E-Mail Removed)> wrote in message
>> news:2BE5C77F-991F-4FCC-B845-(E-Mail Removed)...
>> > Ossie,
>> >
>> > Along those same lines, is there a couple lines of code that will clear
>> > the
>> > contents on all of the rows below the last of row of contiguous data,
>> > even
>> > if
>> > the rows are hidden.
>> >
>> > In other words, a set of data is in A1:J100, and then there are about
>> > 20
>> > blank rows, then some more data starts in A120. I'd want the code to
>> > find
>> > the
>> > last row of the initial contiguous data (100), then delete everything
>> > in
>> > the
>> > spreadsheet below it even if some rows are hidden.
>> >
>> > Thanks again!
>> > Dani
>> >
>> > "OssieMac" wrote:
>> >
>> >> Hi Dani,
>> >>
>> >> Here are 2 options. See the comments for when to use the option.
>> >>
>> >> 'Option 1
>> >> Sub DeleteRows1()
>> >> 'When a specific column will always have data in last row.
>> >>
>> >> Dim lastRow As Long
>> >>
>> >> With Sheets("Sheet1")
>> >> 'Can replace "A" with any column that
>> >> 'will always have data in the last row.
>> >> lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
>> >> .Rows(lastRow - 1 & ":" & lastRow).Delete
>> >>
>> >> End With
>> >>
>> >> End Sub
>> >>
>> >>
>> >> 'Option 2
>> >> Sub DeleteRows2()
>> >>
>> >> 'If it is not know which column
>> >> 'will always have data in the last row.
>> >>
>> >> Dim lastRow As Long
>> >>
>> >> With Sheets("Sheet1")
>> >>
>> >> lastRow = .Cells _
>> >> .Find(What:="*", _
>> >> After:=.Cells(1, 1), _
>> >> LookIn:=xlFormulas, _
>> >> LookAt:=xlPart, _
>> >> SearchOrder:=xlByRows, _
>> >> SearchDirection:=xlPrevious, _
>> >> MatchCase:=False, _
>> >> SearchFormat:=False).Row
>> >>
>> >> .Rows(lastRow & ":" & lastRow - 1).Delete
>> >>
>> >> End With
>> >>
>> >> End Sub
>> >>
>> >> --
>> >> Regards,
>> >>
>> >> OssieMac
>> >>
>> >>
>>
>> .
>>
|
Similar Threads
