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

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

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" <(E-Mail Removed)> wrote in
message news:408CE354-C1ED-4E21-BA30-(E-Mail Removed)...
> 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

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

> 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

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" <(E-Mail Removed)> wrote in
> message news:408CE354-C1ED-4E21-BA30-(E-Mail Removed)...
> > 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
>
> .
>

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" <(E-Mail Removed)> wrote in
message news:7A34907C-14F7-42AB-9475-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in
>> message news:408CE354-C1ED-4E21-BA30-(E-Mail Removed)...
>> > 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
>>
>> .
>>

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

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

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" <(E-Mail Removed)> wrote in message
news:08BCB678-74F1-45F2-812A-(E-Mail Removed)...
>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