Need VB Code to Insert In recorded Excel Macro

[Mike]

Here's the code I recorded:

Columns("K:K").Select
Selection.Find(What:="zzzz", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("K53").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Range("A53:K264").Select
Range("K53").Activate
Selection.Delete Shift:=xlUp

My problem is that the result of the FIND will not always be in K53 as the
row will vary by filesize.

After the FIND cell is located, I'm deleting all data in cells a?? to K264.

So, not to get to verbose, I don't want the macro to contain a static FIND
cell, but want it to be dynamic.

Thanks

 

[Clif McIrvin]

I can't determine preciselt what rules govern your selections; but here
are some ideas you should be able to work with:

At the beginning of the recorded macro insert:

dim foundRow as long
dim foundCol as long
dim lastRow as long

' Define begin / end columns to delete
const beginCol = 1 as long ' Col A == Col 1
const endCol = 11 as long ' Col K == Col 11

(I used constants so that if the rules change in the future you only
need to revise the Const statement(s), not every affected line of code.)

Here's the code I recorded:

Columns("K:K").Select
Selection.Find(What:="zzzz", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
Range("K53").Select

Here you have an Activate followed by a Select ... it would seem that
the Select lost the cell that the Find located for you (unless you
clicked into the found cell?)

Replace > Range("K53").Select with:

with ActiveCell ' remember row and column of found cell
foundRow = .row
foundCol = .column
end with

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Replace
Range("A53:K264").Select > Range("K53").Activate
Selection.Delete Shift:=xlUp

with:

lastRow = Selection.Row ' remember last row of range
range(cells(foundRow, beginCol), cells(lastRow, endCol)).Delete
Shift:=xlUp

My problem is that the result of the FIND will not always be in K53 as
the row will vary by filesize.

After the FIND cell is located, I'm deleting all data in cells a?? to
K264.

So, not to get to verbose, I don't want the macro to contain a static
FIND cell, but want it to be dynamic.

Thanks

So, you end up with:


dim foundRow as long ' Row of Found Cell
dim foundCol as long ' Column of Found Cell
dim lastRow as long ' Row of last Row below Found Cell

' Define begin / end columns to delete
const beginCol = 1 as long ' Col A == Col 1
const endCol = 11 as long ' Col K == Col 11

Columns("K:K").Select
Selection.Find(What:="zzzz", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
with ActiveCell ' remember row and column of found cell
foundRow = .row
foundCol = .column
end with

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
lastRow = Selection.Row ' remember last row of range

range(cells(foundRow, beginCol), cells(lastRow, endCol)).Delete
Shift:=xlUp


HTH!

 

[Mike]

What I didn't make clear is that "lastrow" would be a constant. Other than
that, it worked and I thank you very much Clif!!!!!!


I had to make slight changes to the declarations:


Dim foundRow As Long
Dim foundCol As Long

' Define begin / end columns to delete
' Col A == Col 1
'Col K == Col 11
'lastrow is last possible row in file
Const beginCol = 1
Const endCol = 11
Const lastrow = 264

Also made slight changes to execution:

Columns("K:K").Select
Selection.Find(What:="zzzz", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
With ActiveCell ' remember row and column of found cell
foundRow = .Row
foundCol = .Column
End With

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Range(Cells(foundRow, beginCol), Cells(lastrow, endCol)).Delete
Shift:=xlUp
===================================================================================

 

[Clif McIrvin]

Glad I was able to help out <smile>.

It seems that

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

isn't doing anything useful; the delete that follows changes the
selection, and does *not* use the selection; it uses the
Range(Cells(),Cells()) syntax instead.

That line would have appeared because you used a CTRL+DN while recording
your macro, and is not needed by the VBA code.

By leaving it in, and capturing the row it returned my suggested code
would allow a variable ending row (but an empty cell would have broken
it's expected operation.)

Glad you have it working.

--
Clif

What I didn't make clear is that "lastrow" would be a constant. Other
than that, it worked and I thank you very much Clif!!!!!!


I had to make slight changes to the declarations:


Dim foundRow As Long
Dim foundCol As Long

' Define begin / end columns to delete
' Col A == Col 1
'Col K == Col 11
'lastrow is last possible row in file
Const beginCol = 1
Const endCol = 11
Const lastrow = 264

Also made slight changes to execution:

Columns("K:K").Select
Selection.Find(What:="zzzz", After:=ActiveCell, LookIn:=xlFormulas,
_
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
_
MatchCase:=False, SearchFormat:=False).Activate
With ActiveCell ' remember row and column of found cell
foundRow = .Row
foundCol = .Column
End With

Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

Range(Cells(foundRow, beginCol), Cells(lastrow, endCol)).Delete
Shift:=xlUp
===================================================================================