Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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" wrote in message ... 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 . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Clear contents of last two rows of data on sheet
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" wrote in message ... 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" wrote in message ... 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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
duplicate data in range clear contents | Excel Programming | |||
Macro or VBA to Clear Contents of Rows By Date (Starting At A5) | Excel Programming | |||
clear contents on linked sheet | Excel Programming | |||
Clear rows with no data? | Excel Programming | |||
Macro to clear range contents when cell contents are changed by us | Excel Programming |