Excel 2003 - Macro for Deleting Rows

[Solon]

Greetings,
Still using Excel 2003. Now that I have a macro that will delete rows, AND
do it from the bottom up, I've got another question.

The guy who gave me the macro said he'd never considered doing a "Ctrl+Down"
to simply go to the last used row, then do his search from there, but
figured that there HAS to be a way to get Excel to set that row number as a
variable, then have the macro use that number as it's starting point.

But neither of us can figure out how to get the macro to 'get cell row
number' or whatever could then be used as the starting point.

We can tell the macro to

Selection.End(xlDown).Select

But then how do we tell it to say "Oh, I've gone down this many rows" and
set that as the 'rowEnd' variable.

Any ideas?

Solon

 

[Don Guillett]

You should always post YOUR macro for comment and suggestion.s

lr=cells(rows.count,activecell.column).end(xldown).row

However, you need not and should not select it. Post your macro

 

[Roger Govier]

Hi

Dim lastrow as long

lastrow = Cells(Rows.Count, "A").End(xlUp).Row

Make the column Letter whatever you want to use for finding the lastrow.

 

[Ron Rosenfeld]

Greetings,
Still using Excel 2003. Now that I have a macro that will delete rows, AND
do it from the bottom up, I've got another question.

The guy who gave me the macro said he'd never considered doing a "Ctrl+Down"
to simply go to the last used row, then do his search from there, but
figured that there HAS to be a way to get Excel to set that row number as a
variable, then have the macro use that number as it's starting point.

But neither of us can figure out how to get the macro to 'get cell row
number' or whatever could then be used as the starting point.

We can tell the macro to

Selection.End(xlDown).Select

But then how do we tell it to say "Oh, I've gone down this many rows" and
set that as the 'rowEnd' variable.

Any ideas?

Solon

===============
Dim lastrow As Long
lastrow = Range("A65535").End(xlUp).Row
===================

This is like going to the bottom of column A and pressing <end><uparrow>

Now I don't work with Excel 2007, but I understand that it can have more rows,
and also that the number of rows might vary depending on the environment.

So you would need to figure out what the bottom row is, in order to move up to
the last one.

================================
Dim lastrow As Long
Dim numrows As Long
numrows = Range("A:A").Rows.Count
lastrow = Cells(numrows, 1).End(xlUp).Row
============================
--ron

 

[Gord Dibben]

CTRL + Down goes to cell above first empty cell in the column, not necessarily
to last used row.

See other responses for getting true last row and giving it a numbered value.


Gord Dibben MS Excel MVP

 

[Ron de Bruin]

Hi Solon

You can find all this information on the site I posted in your first thread

 

[Solon]

OHHHHH.. didn't know that little tidbit.. much thanks!

Solon

 

[Solon]

Ron,

As much as I appreciate that, I get very confused looking at the macros on
your site. I've bookmarked it for when I'm feeling a little more confident
in my understanding of macros.

Solon

 

[Solon]

I can do that.. should have thought of it on my own.


Sub Macro1()
'
SearchTerm = "Delete me"
iRowEnd = 3000
iRowStart = 2
'
' Delete unneeded rows
For irows = iRowEnd To iRowStart Step -1
If ActiveSheet.Range("D" & irows) = SearchTerm Then
ActiveSheet.Rows(irows).Delete
End If
Next
End Sub

One of the 'fun' parts of this is that I've never even SEEN the use of ("D"
& irows) to establish a range. I don't know which version he's working with,
but my macro experience is largely with Sendkeys and recording some actions
then reviewing the generated code along with some information sources.....

Anyway, if I can find a way to go to the last used row and start there,
that'd be great. If not, I can manually find that value and enter it into
the macro if needed.
Heck, I may be able to turn the macro on and have it prompt for me to enter
that value..... hmmmm

Solon

 

[Don Guillett]

Sub findanddeletebottomup()
what = "DELETE ME"
For i = Cells(Rows.Count, "d").End(xlUp).Row To 2 Step -1
If UCase(Cells(i, "d")) = what Then Rows(i).Delete
Next i

 

[Solon]

Don,
Nice bit, I think I can even somewhat comprehend it. It seemed to work fine
for "Delete Me" and "Testing", but I find that when I type in our 'what'
value which is "25 - Johnson Parts" (Not the quotes) it doesn't work. If I
just put in "25 - " it works fine, but as soon as I throw in a letter, it
doesn't function. I even tried formatting the data in the column as text.

Any ideas what might cause that?

Solon

 

[Don Guillett]

Trim?? Send a file if you like.

 

[Don Guillett]

It is not what I meant. You should never attach a file to a post to the ng.
I meant to send direct to me. Your problem is that you did not put your what
in all upper case. That is what the ucase( meant. My error in not
explaining. I usually do this to account for some people using Johnson
parts, johnson parts, etc.

"25 - JOHNSON PARTS"

 

[Solon]

Ahh.. sorry about that on the posting.

ucase, huh? That's cool, I like that the option is in there, thanks for the
clarification!

Solon

 

[Solon]

Don,
I took at look at the ucase( thing and wondered how to remove the
requirement (I get these files as outputs from our inventory control system
and never know even if they'll be title case) and it looked like I could
remove the parentheses and ucase to leave just Cells(i, "d") then perhaps it
wouldn't care about the case, and sure enough it worked!

Thanks for the info and the help!

Solon

 

[Don Guillett]

Of course, Just make SURE that YOU don't have a typo.