Delete Rows below a certain cell

  • Thread starter Thread starter Julie
  • Start date Start date
J

Julie

Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie
 
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
 
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie
Click to expand...


.
Click to expand...
 
Which line causes the error?
(as I said - it worked in Excel97)

Make sure that you correct word wrap...
Remove the comment lines and the blank lines...
Go to the Debug menu and select Compile to find
problem areas in the code. Repeat until no problems
are found.

Dim x as Long, y as Long

' Make these one line
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,

LookIn:=xlFormulas, _

' Make these one line
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

--
sb
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie
Click to expand...


.
Click to expand...
Click to expand...
 
Hi Steve,

This is exactly what I have:

Dim x As Long, y As Long

x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

The second line of x is actually on the first row. My
mismatch error is showing within the x portion with the
arrow on the second row. I tried making a minor
adjustment to my Find(What:=....), thinking that was the
problem, but it wasn't. Like you said, it could be that
this may only work I Office 97. I appreciate all of your
help and have a great day!

Julie
-----Original Message-----
Which line causes the error?
(as I said - it worked in Excel97)

Make sure that you correct word wrap...
Remove the comment lines and the blank lines...
Go to the Debug menu and select Compile to find
problem areas in the code. Repeat until no problems
are found.

Dim x as Long, y as Long

' Make these one line
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,

LookIn:=xlFormulas, _

' Make these one line
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

--
sb
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report", After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie


.
Click to expand...
Click to expand...


.
Click to expand...
 
Julie,

Aha! You are missing a line continuation (probably my fault)
This should all be one line
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
Click to expand...

change to:
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row

note the underscore '_'

Also - you can use the single word report (in lower case) to find...

--
sb
Julie said:
Hi Steve,

This is exactly what I have:

Dim x As Long, y As Long

x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

The second line of x is actually on the first row. My
mismatch error is showing within the x portion with the
arrow on the second row. I tried making a minor
adjustment to my Find(What:=....), thinking that was the
problem, but it wasn't. Like you said, it could be that
this may only work I Office 97. I appreciate all of your
help and have a great day!

Julie
-----Original Message-----
Which line causes the error?
(as I said - it worked in Excel97)

Make sure that you correct word wrap...
Remove the comment lines and the blank lines...
Go to the Debug menu and select Compile to find
problem areas in the code. Repeat until no problems
are found.

Dim x as Long, y as Long

' Make these one line
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,

LookIn:=xlFormulas, _

' Make these one line
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

--
sb
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
message
Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On
row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in
column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie


.
Click to expand...


.
Click to expand...
Click to expand...
 
Hey Steve,

At first, the code still didn't work, but then I added
Columns("C:C").Select before the x and y statements. It
finally worked! I think that the code was looking at the
ActiveCell for the macro that I have in right before this
one. So I put in the Select statement and it worked.
Thanks for all of your help and patience! Have a great
day!

Julie
-----Original Message-----
Julie,

Aha! You are missing a line continuation (probably my fault)
This should all be one line
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
Click to expand...

change to:
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row

note the underscore '_'

Also - you can use the single word report (in lower case) to find...

--
sb
Hi Steve,

This is exactly what I have:

Dim x As Long, y As Long

x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

The second line of x is actually on the first row. My
mismatch error is showing within the x portion with the
arrow on the second row. I tried making a minor
adjustment to my Find(What:=....), thinking that was the
problem, but it wasn't. Like you said, it could be that
this may only work I Office 97. I appreciate all of your
help and have a great day!

Julie
-----Original Message-----
Which line causes the error?
(as I said - it worked in Excel97)

Make sure that you correct word wrap...
Remove the comment lines and the blank lines...
Go to the Debug menu and select Compile to find
problem areas in the code. Repeat until no problems
are found.

Dim x as Long, y as Long

' Make these one line
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,

LookIn:=xlFormulas, _

' Make these one line
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

--
sb
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells (xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
message
Hi! I have a question regarding macros in Excel. Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that contain
data?

For example:
I have a report that I have imported into Excel. On
row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in
column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Julie,

Glad you got it to work! But I just noticed another ommission on my part
It should work without the select.

This should all be one line:

x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row

need a space and underscore on the first and third lines
x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*", _
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row

--
sb
Hey Steve,

At first, the code still didn't work, but then I added
Columns("C:C").Select before the x and y statements. It
finally worked! I think that the code was looking at the
ActiveCell for the macro that I have in right before this
one. So I put in the Select statement and it worked.
Thanks for all of your help and patience! Have a great
day!

Julie
-----Original Message-----
Julie,

Aha! You are missing a line continuation (probably my fault)
This should all be one line
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
Click to expand...

change to:
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False).Row

note the underscore '_'

Also - you can use the single word report (in lower case) to find...

--
sb
Hi Steve,

This is exactly what I have:

Dim x As Long, y As Long

x = Columns("C:C").Find(What:="*REPORTS TO INCLUDE*",
After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

The second line of x is actually on the first row. My
mismatch error is showing within the x portion with the
arrow on the second row. I tried making a minor
adjustment to my Find(What:=....), thinking that was the
problem, but it wasn't. Like you said, it could be that
this may only work I Office 97. I appreciate all of your
help and have a great day!

Julie
-----Original Message-----
Which line causes the error?
(as I said - it worked in Excel97)

Make sure that you correct word wrap...
Remove the comment lines and the blank lines...
Go to the Debug menu and select Compile to find
problem areas in the code. Repeat until no problems
are found.

Dim x as Long, y as Long

' Make these one line
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,

LookIn:=xlFormulas, _

' Make these one line
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _

MatchCase:=False).Row
y = Selection.SpecialCells(xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

--
sb
Hi Steve,

For some reason, I am getting a mismatch error on this
example. I tried changing a couple of things and I
still
got the same error. Any suggestions?

Thanks for your help!

Julie
-----Original Message-----
Julie,

This worked in Excel97 [watch word wrap]

Dim x As Long, y As Long
'
x = Columns("C:C").Find(What:="report",
After:=ActiveCell,
LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows,
SearchDirection:=xlNext, _
MatchCase:=False).Row
y = Selection.SpecialCells (xlCellTypeLastCell).Row
Range(Rows(x), Rows(y)).EntireRow.Delete

If 'report' appears in any column, change
Columns("C:C").
to
Cells.
--
sb
message
Hi! I have a question regarding macros in Excel.
Can
you create a macro to go to a certain cell and then
delete that cell and the cells after that that
contain
data?

For example:
I have a report that I have imported into Excel. On
row
20, in Column "C", I have a heading called "**Report
Status**". I want the macro to go to that cell and
delete all the rows below it that contain data.

FYI...The heading is not always on row 20 in
column "C".
The row varies each time I import data!

I appreciate any help that can be given!

Julie


.



.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top