Hi.
I'm using v 2003.
I need a couple of lines of code that will delete or clear the contents of
the last two rows on a sheet that have data in them. In other words, if there
are 800 lines of data and I want rows 799 and 800 to be deleted or contents
cleared. The number of the last row varies everytime I run the report.
Can anyone help? Thanks!
Dani
|
|
0
|
|
|
|
Reply
|
 Utf
|
3/11/2010 7:06:01 PM |
|
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
|
|
|
0
|
|
|
|
Reply
|
Utf
|
3/11/2010 7:36:02 PM
|
|
The first option works great! Thanks! I should've specified that there is
always data in column A.
Appreciate your help!
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
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
3/11/2010 7:47:01 PM
|
|
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
>
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
3/11/2010 8:14:01 PM
|
|
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" <Danielle@discussions.microsoft.com> wrote in message
news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
> 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
>>
>>
|
|
|
0
|
|
|
|
Reply
|
Rick
|
3/11/2010 9:15:54 PM
|
|
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" <Danielle@discussions.microsoft.com> wrote in message
> news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
> > 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
> >>
> >>
>
> .
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
3/12/2010 7:23:01 PM
|
|
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" <Danielle@discussions.microsoft.com> wrote in message
news:A7E7F034-66CD-45A2-BD28-0E0232FB063B@microsoft.com...
> 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" <Danielle@discussions.microsoft.com> wrote in message
>> news:2BE5C77F-991F-4FCC-B845-F64A02F879CB@microsoft.com...
>> > 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
>> >>
>> >>
>>
>> .
>>
|
|
|
0
|
|
|
|
Reply
|
Rick
|
3/12/2010 8:29:27 PM
|
|
|
6 Replies
186 Views
(page loaded in 0.311 seconds)
Similiar Articles: If cell value is greater than another cell value, clear contents ...... by setting the range as B2:B & Last Row ... macro that looks in column B on the data sheet ... is greater than another cell value, clear contents ... Return last and ... Macro to merge 3 rows multi times, columns A:K only - microsoft ...... one sheet would be through row 32 ( the last of ... the data in Sheet i.e. all the rows of column A6 from sheet-2 ... want to clear contents of columns A - K of those rows ... vba to delete rows - microsoft.public.excel.worksheet.functions ...I need to remove the first 3 and last 3 rows. The ... Clear all rows except first two - microsoft.public.excel ... ... Empty Formfield Contents - microsoft.public.word ... Need help with macro to copy, paste, and clear cell values ...... of this message on an inserted sheet >> 2 ... Macro to clear contents of certain cells ... with macro to copy, paste, and clear cell values ... 'macro looks at last row ... Identify last row containing data - microsoft.public.excel ...Hi, How can I identify the last row containing data on a sheet where the last entry could appear in ... this may just be misinterpreting what you mean by "any content ... VBA to merge worksheets (only populated rows) - microsoft.public ...... clear the current worksheet (i.e. consolidated plan) of all data except the two header rows ... only the last few rows ... into one sheet ... and data starting from row 2. Return last and second last "populated" cell in column ...Excel 2003: macro to add data to next empty row ... Return last and second last ... If cell value is greater than another cell value, clear contents ... Return last ... UserForm Delete Sheet Columns - microsoft.public.excel.programming ...Sheet2 retains the input data in 2 columns starting at Col D. Row 1 ... Sheet - I have a work sheet with 3 columns in each Last Name... ... ... VBA code to delete cell contents ... Excel 2003 Macro - Finding rows with data to copy - microsoft ...... this message on an inserted sheet 2. You give me the newsgroup and the subject line 3. ... the headers) and go to the last row that has data ... Too many rows displaying on spreadsheet - microsoft.public.mac ...... Clear>All... 2) Excel maintains a "Last ... content (either data, or formatting, or both). To Find the Last Cell in a Sheet ... data. If you know the last data is in Row ... 7/30/2012 3:41:30 PM
|
Search |
Post Question |
Groups |
Stream |
About |
Register
32 followers. 