Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Hello:
As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Hi,
How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Since the word row in Column A that the word "Grand" is in was put there by
your code, then you already know the row number for it... just store that **row number** in a variable at the same time you put the word "Grand" into its cell, then at the end of your code, you can execute this statement to clear that row downward... Range(RowWithGrandVariable & ":" & Rows.Count).Clear Of course, use your actual variable's name for the example variable name of RowWithGrandVariable that I used above. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Nope....didn't work.
"Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Nope....didn't work.
That's not a very good description of what went wrong!! Try this modification Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row FirstRow = Marker.Row ..Rows(FirstRow & ":" & LastRow).ClearContents End With End Sub Mike "childofthe1980s" wrote: Nope....didn't work. "Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Again, per my posting, I cannot rely on an exact row number.
"Rick Rothstein" wrote: Since the word row in Column A that the word "Grand" is in was put there by your code, then you already know the row number for it... just store that **row number** in a variable at the same time you put the word "Grand" into its cell, then at the end of your code, you can execute this statement to clear that row downward... Range(RowWithGrandVariable & ":" & Rows.Count).Clear Of course, use your actual variable's name for the example variable name of RowWithGrandVariable that I used above. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Again, per my posting, I didn't say to rely on an exact row number... what I
said was to remember the row number that **your code** used when it placed the word "Grand" into whatever cell it placed the word Grand into. Your code does do the placing of the word Grand into a cell, right? Just so you understand our problems in trying to answer your question... we cannot see your code (because you didn't post it), so we have to make guesses as to what you have and what it is you are doing with it. If you could post your code, that would make answering your question so much easier. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Again, per my posting, I cannot rely on an exact row number. "Rick Rothstein" wrote: Since the word row in Column A that the word "Grand" is in was put there by your code, then you already know the row number for it... just store that **row number** in a variable at the same time you put the word "Grand" into its cell, then at the end of your code, you can execute this statement to clear that row downward... Range(RowWithGrandVariable & ":" & Rows.Count).Clear Of course, use your actual variable's name for the example variable name of RowWithGrandVariable that I used above. -- Rick (MVP - Excel) "childofthe1980s" wrote in message ... Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
I would suggest that the reason the code failed is because there are blank
cells in column A between the last row of junk data and the word Grand and it has not correctly found the last row. Using the last row on the worksheet will eliminate that possibility. Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long Dim Marker As Range With Worksheets("Sheet1").Range("a:a") 'Change to suit Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) MsgBox Marker.Address FirstRow = Marker.Row LastRow = .Rows.Count End With Rows(FirstRow & ":" & LastRow).ClearContents End Sub -- Regards, OssieMac "Mike H" wrote: Nope....didn't work. That's not a very good description of what went wrong!! Try this modification Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row FirstRow = Marker.Row .Rows(FirstRow & ":" & LastRow).ClearContents End With End Sub Mike "childofthe1980s" wrote: Nope....didn't work. "Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
I left a msgbox in my previously posted code that I was using during testing
and as an afterthought it is a good idea to include code to handle "Grand not found" so that the code will not error if run twice or grand is missing for any other reason. The following code is an improvement. Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long Dim Marker As Range With Worksheets("Sheet1").Range("a:a") 'Change to suit Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Marker Is Nothing Then FirstRow = Marker.Row LastRow = .Rows.Count Else MsgBox "Grand not found. No rows deleted" Exit Sub End If End With Rows(FirstRow & ":" & LastRow).ClearContents End Sub -- Regards, OssieMac "Mike H" wrote: Nope....didn't work. That's not a very good description of what went wrong!! Try this modification Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row FirstRow = Marker.Row .Rows(FirstRow & ":" & LastRow).ClearContents End With End Sub Mike "childofthe1980s" wrote: Nope....didn't work. "Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
I still say the simplest solution is for the OP to have his macro remember
(in a variable) what the last line of "real" data was BEFORE the code starts putting "junk" data in the rows below it. That way, no searching is required at all... this single line of code would clean up all the "junk" data in one fell swoop... Range((LastRealDataRow + 1) & ":" & Rows.Count).Clear The Clear operation should automatically stop at the last row of the UsedRange, so I think using Rows.Count is sufficient (athough it might be necessary to qualify the range references with a worksheet reference depending on information the OP has not given us). -- Rick (MVP - Excel) "OssieMac" wrote in message ... I would suggest that the reason the code failed is because there are blank cells in column A between the last row of junk data and the word Grand and it has not correctly found the last row. Using the last row on the worksheet will eliminate that possibility. Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long Dim Marker As Range With Worksheets("Sheet1").Range("a:a") 'Change to suit Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) MsgBox Marker.Address FirstRow = Marker.Row LastRow = .Rows.Count End With Rows(FirstRow & ":" & LastRow).ClearContents End Sub -- Regards, OssieMac "Mike H" wrote: Nope....didn't work. That's not a very good description of what went wrong!! Try this modification Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row FirstRow = Marker.Row .Rows(FirstRow & ":" & LastRow).ClearContents End With End Sub Mike "childofthe1980s" wrote: Nope....didn't work. "Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Hi OssieMac
You may be correct but I don't think that's the issue LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Will find the last row even if there are blanks. I think the issue with my first code was poor coding. From my first post LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents Both of those lines will fail if the active sheet doesn't contain the data From my second post LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row ..Rows(FirstRow & ":" & LastRow).ClearContents Note these lines are now inside the with statement for sheet2 so the active sheet doesn't matter. I think Rick hit the nail on the head when he makes the valid point "we cannot see your code (because you didn't post it), so we have to make guesses as to what you have and what it is you are doing with it. If you could post your code, that would make answering your question so much easier." I doubt we will ever know now because both Rick & I appear to be off the OP's Christmas card list but thanks for your comments. The OP may respond to you and hopefully his/her problem will be resolved Happy Christmas Ossiemac Mike "OssieMac" wrote: I left a msgbox in my previously posted code that I was using during testing and as an afterthought it is a good idea to include code to handle "Grand not found" so that the code will not error if run twice or grand is missing for any other reason. The following code is an improvement. Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long Dim Marker As Range With Worksheets("Sheet1").Range("a:a") 'Change to suit Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) If Not Marker Is Nothing Then FirstRow = Marker.Row LastRow = .Rows.Count Else MsgBox "Grand not found. No rows deleted" Exit Sub End If End With Rows(FirstRow & ":" & LastRow).ClearContents End Sub -- Regards, OssieMac "Mike H" wrote: Nope....didn't work. That's not a very good description of what went wrong!! Try this modification Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With LastRow = .Cells(Cells.Rows.Count, "A").End(xlUp).Row FirstRow = Marker.Row .Rows(FirstRow & ":" & LastRow).ClearContents End With End Sub Mike "childofthe1980s" wrote: Nope....didn't work. "Mike H" wrote: Hi, How about this Sub Clear_Junk() Dim FirstRow As Long, LastRow As Long With Worksheets("Sheet2") 'Change to suit With .Range("a:a") Set Marker = .Cells.Find(What:="Grand", _ After:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ LookAt:=xlPart, _ SearchOrder:=xlByRows, _ SearchDirection:=xlNext, _ MatchCase:=False) End With End With FirstRow = Marker.Row LastRow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Rows(FirstRow & ":" & LastRow).ClearContents End Sub Mike "childofthe1980s" wrote: Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
adding lines of code to a macro to delete rows
Sub test()
Dim R As Long, RL As Long RL = ActiveSheet.UsedRange.Rows.Count R = Columns(1).Find(What:="Grand", _ After:=Range("A1"), _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlPrevious, _ MatchCase:=False).Row Range("A" & R & ":A" & RL).EntireRow.Delete End Sub HTH. Best wishes Harald "childofthe1980s" wrote in message ... Hello: As the last step of a macro that I have written, I need all rows at the end of this spreadsheet to be deleted. (The rows at the end of the spreadsheet have bogus data, and I need to get rid of all of those rows and leave just rows of "real" data--long story short.) Specifically, the row after the last row of "real" data is a cell in column A that simply contains the word "Grand". How do I put in code at the end that says "delete all rows at the end of this spreadsheet beginning with this last row that contains just the word 'Grand' at the end of column A"? At the moment, "Grand" is in cell A667. But, that is not going to be the case everytime that I run this macro for this data. Next time, "Grand" could be in cell A748, A901, A820, who knows?.......So, I cannot simply put in code that says "delete all rows at the end of this spreadsheet beginning with the row at cell A667". Thanks! childofthe1980s |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to delete rows which is blank and which has ======== ( lines) | Excel Discussion (Misc queries) | |||
Macro to delete lines | Excel Programming | |||
Application Crash Whie adding Code lines using VBA | Excel Programming | |||
adding code lines with vba code | Excel Programming | |||
Delete Rows Between Border Lines | Excel Programming |