Excel 2003 - Macro for Deleting Rows
 

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

Excel 2003 - Macro for Deleting Rows
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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.

--

Regards
Roger Govier

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

On Fri, 7 Dec 2007 19:05:42 -0500, "Solon" wrote:

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

Excel 2003 - Macro for Deleting Rows
 

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

On Fri, 7 Dec 2007 19:05:42 -0500, "Solon" wrote:

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,

Excel 2003 - Macro for Deleting Rows
 

Hi Solon

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message ...
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

Excel 2003 - Macro for Deleting Rows
 

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

Solon
"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
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

On Fri, 7 Dec 2007 19:05:42 -0500, "Solon"
wrote:

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,

Excel 2003 - Macro for Deleting Rows
 

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

"Ron de Bruin" wrote in message
...
Hi Solon

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

Trim?? Send a file if you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
Don,
I attached it here, hope that's what you meant. I sanitized it first, of
course and trimmed it down to 200 lines with 50/50 mix of Johnson Parts
and third party stuff.

Take a look, I'm not dead in the water or anything, as the other macro
functions, but the 'why' of this little bit of difficulty has me all
curious. Here I go from not understanding the command to not understanding
why it operates the way it does... very odd.

Thanks,

Solon

"Don Guillett" wrote in message
...

Trim?? Send a file if you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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

"Don Guillett" wrote in message
...

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"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
Don,
I attached it here, hope that's what you meant. I sanitized it first, of
course and trimmed it down to 200 lines with 50/50 mix of Johnson Parts
and third party stuff.

Take a look, I'm not dead in the water or anything, as the other macro
functions, but the 'why' of this little bit of difficulty has me all
curious. Here I go from not understanding the command to not
understanding why it operates the way it does... very odd.

Thanks,

Solon

"Don Guillett" wrote in message
...

Trim?? Send a file if you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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" wrote in message
...

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"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
Don,
I attached it here, hope that's what you meant. I sanitized it first, of
course and trimmed it down to 200 lines with 50/50 mix of Johnson Parts
and third party stuff.

Take a look, I'm not dead in the water or anything, as the other macro
functions, but the 'why' of this little bit of difficulty has me all
curious. Here I go from not understanding the command to not
understanding why it operates the way it does... very odd.

Thanks,

Solon

"Don Guillett" wrote in message
...

Trim?? Send a file if you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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

Excel 2003 - Macro for Deleting Rows
 

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...

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"

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
Don,
I attached it here, hope that's what you meant. I sanitized it first, of
course and trimmed it down to 200 lines with 50/50 mix of Johnson Parts
and third party stuff.

Take a look, I'm not dead in the water or anything, as the other macro
functions, but the 'why' of this little bit of difficulty has me all
curious. Here I go from not understanding the command to not
understanding why it operates the way it does... very odd.

Thanks,

Solon

"Don Guillett" wrote in message
...

Trim?? Send a file if you like.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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" wrote in message
...
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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Solon" wrote in message
...
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