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
0
Utf
12/24/2009 2:41:01 PM
excel.programming 6508 articles. 2 followers. Follow

11 Replies
607 Views

Similar Articles

[PageSpeed] 42

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
0
Utf
12/24/2009 3:21:01 PM
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" <childofthe1980s@discussions.microsoft.com> wrote in 
message news:408CE354-C1ED-4E21-BA30-E60594E319F9@microsoft.com...
> 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 

0
Rick
12/24/2009 3:26:08 PM
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
0
Utf
12/24/2009 3:45:02 PM
> 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
0
Utf
12/24/2009 3:56:05 PM
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" <childofthe1980s@discussions.microsoft.com> wrote in 
> message news:408CE354-C1ED-4E21-BA30-E60594E319F9@microsoft.com...
> > 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 
> 
> .
> 
0
Utf
12/24/2009 4:19:02 PM
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" <childofthe1980s@discussions.microsoft.com> wrote in 
message news:7A34907C-14F7-42AB-9475-E5F7CE8D8650@microsoft.com...
> 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" <childofthe1980s@discussions.microsoft.com> wrote in
>> message news:408CE354-C1ED-4E21-BA30-E60594E319F9@microsoft.com...
>> > 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
>>
>> .
>> 

0
Rick
12/24/2009 4:29:02 PM
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
0
Utf
12/24/2009 8:18:01 PM
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
0
Utf
12/24/2009 8:30:01 PM
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" <OssieMac@discussions.microsoft.com> wrote in message 
news:08BCB678-74F1-45F2-812A-2DFF66606252@microsoft.com...
>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 

0
Rick
12/24/2009 8:36:34 PM
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
0
Utf
12/24/2009 8:50:01 PM
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" <childofthe1980s@discussions.microsoft.com> wrote in 
message news:408CE354-C1ED-4E21-BA30-E60594E319F9@microsoft.com...
> 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 

0
Harald
12/27/2009 7:23:42 AM
Reply:

Similar Artilces:

adding extra marker styles
how do we add extra marker styles into an excel chart? we have used all the ones it has given us in 2003, but there use3d to be many more in 2002, how do we get them back? Hi, Here is an explanation on how to add custom markers. http://peltiertech.com/Excel/ChartsHowTo/CustomMarkers.html I don't think the marker style have changed since xl2000. Cheers Andy Grizzabella wrote: > how do we add extra marker styles into an excel chart? we have used all the > ones it has given us in 2003, but there use3d to be many more in 2002, how do > we get them back? -- Andy Pope, Micro...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Macros not performing correctly
I recorded a macro to place a vlookup formula in four cells that are side by side. When I run the macro it only performs the first task, it does not go on to do the other three. I used the tab key to go to the next cell while recording the macro, I don't know if that's why it's not working or if it's something else. The script looks like this: Sub EERates() ' ' EERates Macro ' Macro recorded 3/4/2005 by BCBSOK ' ' ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Sheet1!R3C1:R102C2,2,0)" Range("F15").Select ActiveCell.FormulaR1C...

Coloring a row
I have a spreadsheet and I want to have cells colored from column A to K if cell h is not blank. So if h3 has a date in it I want A3:K3 to be say light blue. This is for Office 2003. I can do it with conditional formating in 2007, but my work place doesn't have 2007. I did use column L and put an if statement to give a true or false in the cell depending on if the cell in col. h was empty or not. Any ideas how to get this to work? Hi John This sort of thing will work in 2003 conditional formating. In Cell A3 go to Format - conditional formattting. Formula is Paste...

Adding support for mfc / atl types in a Win32 project
MSDN says this about adding support for classes shared between atl & mfc (I'm just after points and rects btw)... just add the header ( atltypes.h ). I'm getting linker errors for these classes though. Is there a lib I need to add? Any screwy build options need changing? It's in VC 2003. In the project wizard I note that atl and mfc support aren't available when creating an empty Win32 application. Ta. On 26 May 2006 07:42:09 -0700, Simon L wrote: > MSDN says this about adding support for classes shared between atl & > mfc (I'm just after points and rect...

Problem with macro assigned to custom toolbar button
Problem with macro assigned to custom toolbar button I am having a probelm with macros assigned to a custom button on custom button. I am using excel 2000. 1. I have created a macro called "Jump" in a workbook named "Test1" 2. I then created a custom toolbar and added a custom toolbar button t that toolbar. 3. Through the customize dialog box I assign the Macro "Jump" to th custom button. Everything works fine UNTIL. If I do a SAVE AS for the workbook "Test1" and call it something els like "Test2" and then close "Test2" an...

Excel Grid Lines in Outlook Preview Pane
Is there any way to send a spreadsheet to a mail recipient in the body of the message and not have the grid lines show up in the Outlook preview pane when the recipient views the e-mail? If you open the e-mail in Outlook, the grid lines disappear, but in the preview pane they are there. Using Excel and Outlook 2003. Uzytkownik "Kimberly" <Blackrose73@gmail.com> napisal w wiadomosci news:1143658600.180739.308310@v46g2000cwv.googlegroups.com... > Is there any way to send a spreadsheet to a mail recipient in the body > of the message and not have the grid lines show up ...

Can you delete Business Alerts?
I cannot see any way to delete Business Alerts, can someone tell me how? I am using GP 8.0 -- Sheri Salomone THANKS! Try going to Cards --> System --> Business Alerts. -- Charles Allen, MVP "Sheri Salomone" wrote: > I cannot see any way to delete Business Alerts, can someone tell me how? > I am using GP 8.0 > -- > Sheri Salomone > THANKS! woo hoo! Thank you! -- Sheri Salomone THANKS! "Charles Allen" wrote: > Try going to Cards --> System --> Business Alerts. > -- > Charles Allen, MVP > > > > "Sheri Salo...

Duplicate Rows
I have an extract from a student information system in Excel that looks like this. Student Class Grade Quarter John Chemistry 70 1 John Chemistry 80 2 John Math 95 1 John Math 100 2 Alice Chemistry 67 1 Alice Chemistry 47 2 Alice Math 88 1 Alice Math 85 2 What I would like is this: John 70 80 95 100 Alice 67 47 88 85 However, since there are hundreds of students, this would be an extreme pain to do by hand. Is there any built-in formula or function in Excel that can do this? What is it that you actually want to do? (The best approach depends on what your desired end r...

How do you delete an Assembly Item?
I have an assembly item code, and I want to delete it to make it inactive. I cannot find any help on this, nor can I actually FIND the assembly item. Please help!! This is a multi-part message in MIME format. ------=_NextPart_000_0461_01C6F844.55DE89B0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable Nutribodies, You can't make an Assembly inactive as its not really an item, just a = reference to a group of items. You can delete it though. One way of finding items is by their Item type. Click on the Item Type = column header and th...

Add rows automatically? Accordion
Is there a way to automatically add/show rows that have data? I have a data entry sheet. Then I have a report. The report pulls data from the entry sheet. If there is no data for a specific line/row item, is there a way to automatically hide or not show the row(s) with no data? Thanks Thanks can I have more than one autofilter on a sheet? Sloth wrote: > Use the filter function > Select the data and click on... > Data->Filter->Autofilter > This should make an arrow appear at the top of the data (in the header row). > click the arror and select "Nonblanks"....

C# Adding Days to a Date
Hello, I have 2 objects: objContract.activeon and objContract.expireson. I am trying to add 364 days to objContract.activeon and assign it to the value of objContractexpireson. ---------------------------------------------------------- // Contract Start Date DateTime dt = DateTime.Now; objContract.activeon = new CrmDateTime(); int iFound = 0; string sTemp = ""; if (objAccount.paymenttermscode.Value == 1) // due on receipt - use Todays Date { objContract.activeon = objInvoice.CFDinvoicedate; } else // ...

VB Macro #2
I have a macro that is generating Excel files. When the macro runs, it saves two different tabs in the workbooks as values. It needs to test and wait until the formulas that are pulling amounts from another database are correctly populated with the amounts. Right now, I am getting N/A# instead of amounts for some, but not all, of the files. I think the problem is that the formulas have not completed pulling the amounts before the macro pastes that tab as zeros. (N/A# is what appears when the formulas have not completed calculating - once they have completed, the N/A# is replaced ...

Ugly lines between emails in viewing pane
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: Intel Email Client: pop I have Outlook at work and you can turn these lines off but I cant find anywhere to turn these off in Entourage 2008. They weren't there in Entourage 2004. Is there anyway of turning these off to get a clean view? I've looked in preferences and customising only seems to extend to choice of columns. Andrea AndreaA@officeformac.com wrote: > I have Outlook at work and you can turn these lines off but I cant > find anywhere to turn these off in Entourage 2008. They weren't the...

Delete dead mailbox from active directory
We have four exchange servers - two E2K and two E2k3. One of the E2k server just died due to corrupt array control. We had no mailboxes or anything else on that server. One of our administrator must have created one mailbox on that server by mistake and he never realized that until now. server died and we deleted that mailbox from active directory but, when i try to remove the server from system manager i get message saying can't remove because there are mailboxes associated with this information store. Is there any other way i can remove that server from active directory or s...

Formatting a subtotal line with a macro
I already have in use a macro that runs a sort and subtutal function. I would like to format the subtotal row using visual basic since there is already a macro in place. Hi Look at this: ActiveCell.Font.Bold = True Regards, Per "DJDKAL" <daniel.kalfayan@hotmail.com> skrev i meddelelsen news:A86040B0-B4C4-4E11-A80C-E80132C9710F@microsoft.com... >I already have in use a macro that runs a sort and subtutal function. I > would like to format the subtotal row using visual basic since there is > already a macro in place. This is what I've g...

best fit line
I have a line graph with 6 series plotted (I.e. 6 different lines--sales on y, months of x). How do i add a best fit line to the chart? When i select Add Treanline, it only lets you select one series (and I'm assuming the result you get is a linear y=mx+b line....i 'm looking for the best fit line for serveral series... Thanks. jeremy -- Message posted via http://www.officekb.com since it is a line chart, there are several things you could try. If it is justified, you could generate an average of the data points for each x point and plot it. Make a trend line of the average th...

how can you add more rows in excel 2000?
Is there a way to add more rows to excel's already 65536 rows? In other words can you expand excel to have more rows then it already has. Impossible, sorry. HTH. Best wishes Harald "Khody" <Khody@discussions.microsoft.com> skrev i melding news:8A9B7CC9-6832-47D4-830C-7DE692D5616F@microsoft.com... > Is there a way to add more rows to excel's already 65536 rows? In other > words can you expand excel to have more rows then it already has. Unfortunately, you can't increase the number of rows. Versions 2002 and 2003 have the same limitation. tj "Kh...

Mass Delete Contents of Inbox
How can I delete contents of inbox all at once. It would take me all week if I had to do it manually. It is mainly notifications on undeliverables to the system administrator. Edit | Select All, then press Delete. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "Adin" <nnunnallee@hotmail.com> wrote in message news:%23hhkBfWfEHA.592@TK2MSFTNGP11.phx.gbl... > How can I delete contents of inbox all at once. It would take me all week &g...

UNABLE TO FWD ITEMS TO DELETED ITEMS
Can't delete an item from the inbox or anywhere else except the DELETED box. Receive the error: The messaging interface has returned an unknown error. If the problem persists restart Outlook. Last date known that could successfully delete items 12/12/03. Don't know what happened at the time as I was called in after the event. Have restarted Outlook - no luck. Checked Knowledge base - recommended installing SP1. As it already exists on ws,won't let me overide existing SP1. Possible causes as per Knowledge base not the causes in this instance. Does anyone have any suggestion...

unable to Delete shape
I am using Visio 2000 (6.01245), if i select a shape and press delete button on keyboard then shape dosent delete.Can anyone tell what is this problem.How can i resolve this problem. Kindly Help Asif Does object have gray boxes in corners when selected? If so, object has "protection". Menu/format/protection/uncheck/OK "Asif" <Asif@discussions.microsoft.com> wrote in message news:289D3FD5-21DC-4584-9F0E-36CC56FF25B2@microsoft.com... >I am using Visio 2000 (6.01245), if i select a shape and press delete >button > on keyboard then shape dosent dele...

Fulfillment type orders
We have orders at the initial status (just entered) and the options to allow delete documents and void documents are enabled on the Sales Order Type. However, we can't get a void or delete button unless we move the fulfillment order through the confirm process to an "Invoice" to get a Delete? What's up with this? Very annoying. Jana, I know it is subtle and wish there was a better way to display it, but the Void option only applies to Invoice type IDs and not Fulfillment Order type IDs ("Void Invoice Documents" vs. "Delete Documents"). Since both t...

referencing lines and series points
what is the VBA code for referencing to individual points in a line on line graph? i would like to create a macro to change indiviual points shapes and the color or the lines in between them to different color depending on different parameters. is this possible -- medicenpringle ----------------------------------------------------------------------- medicenpringles's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1645 View this thread: http://www.excelforum.com/showthread.php?threadid=39101 Ther easiest way to do this is to first record a macro doing what y...

Can't re-enter a previously deleted User ID
We changed the spelling of a User ID (applewicks to appelwicks) and then deleted it (since he couldn't remember his password and the button for password was greyed out so we couldn't change it.) And now we can't re-enter the same user ID even though it doesn't appear in the window any longer. Here is the error we get: ODBC SQL server driver: The log in appelwicks already exists. Thanks! I believe you have to delete the old ID through Enterprise Manager as well. "cliffs" wrote: > We changed the spelling of a User ID (applewicks to appelwicks) and then ...

mysession & deleting a line item on the pos screen
I want to automatically have my dll delete a line on the pos screen. So I have tried to do a myssion.transaction.entries(1).Delete and other sorts of things. Does anyone know of a way to accomplish this? Thanks in advance for any help on this. Hi, Use code like this: For Each item In mysession.Transaction.entries If FindItem(item.item.id, item.quantity, item.item.description) Then mysession.Transaction.entries.remove(item) End If Next "ClothingStore" wrote: > I want to automatically have my dll delete a line on the pos screen. ...