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

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
"Julie" wrote in
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


.

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


.

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


.



.

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

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


.



.