Macro that deletes and pastes filterd rows and then some

C

CYaYa

Good afternoon,

I have set-up the following macro to do a number of tasks between two
worksheets in a workbook. It does what I need it to do, however I would like
to expand the capabilties of it and "fine tune" it abit.

Sheets("Current Unapplied").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Style = "Comma"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Sheets("Unapplied Copy").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 958
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Current Unapplied'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Current Unapplied").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Unapplied Copy'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Unapplied Copy").Select
Range("A1").Select
End Sub

The first issue I need help with is the following part of the macro (which
will also be used on the other parts that do the smae process):

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select

Is there any code I can use so instead of the range going to 5,000 the range
will stop where the data in cell A stops? I would also like to note that
every week the report varies in size (that is why I set the range to 5,000,
because I know the report will never be that large).

The next two issues deal with expanding the macro:

Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"

This part does what I need it to I would just like to expand the macro at
this point. I would like to insert code here to have the macro delete the
rows with #N/A, and then reset the filter on field 12 to ALL, then go to the
row below the row of last data in cell A.


Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"

Again this does what I need it to I would just like to expand it. I would
like to insert code to have the macro copy the rows with #N/A and paste them
below the last row of data on the sheet named "Unapplied Copy"

I greatly appreciate any help that can be provided.

Chad
 
R

ryguy7272

It would behoove you to delete all that 'ActiveWindow.ScrollRow' and
'ActiveWindow.ScrollColumn' stuff. It comes from recording a macro, and as
far as I can tell, It serves no practical purpose.

Find the end of a list:
here are a couple different ways:

this will select the cell. it's not a good practice to select, but just used
as
an illustration here.

range(worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address).Select

or if you just want A5 returned:

lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address(0,0)

Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Since you are new to VBA in Excel, it may take a bit of work, and some
effort, to get a handle on this stuff, but you CAN do this. Just keep at it
and don't give up.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


CYaYa said:
Good afternoon,

I have set-up the following macro to do a number of tasks between two
worksheets in a workbook. It does what I need it to do, however I would like
to expand the capabilties of it and "fine tune" it abit.

Sheets("Current Unapplied").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Style = "Comma"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Sheets("Unapplied Copy").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 958
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Current Unapplied'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Current Unapplied").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Unapplied Copy'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Unapplied Copy").Select
Range("A1").Select
End Sub

The first issue I need help with is the following part of the macro (which
will also be used on the other parts that do the smae process):

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select

Is there any code I can use so instead of the range going to 5,000 the range
will stop where the data in cell A stops? I would also like to note that
every week the report varies in size (that is why I set the range to 5,000,
because I know the report will never be that large).

The next two issues deal with expanding the macro:

Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"

This part does what I need it to I would just like to expand the macro at
this point. I would like to insert code here to have the macro delete the
rows with #N/A, and then reset the filter on field 12 to ALL, then go to the
row below the row of last data in cell A.


Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"

Again this does what I need it to I would just like to expand it. I would
like to insert code to have the macro copy the rows with #N/A and paste them
below the last row of data on the sheet named "Unapplied Copy"

I greatly appreciate any help that can be provided.

Chad
Click to expand...
 
C

CYaYa

Thanks Ryan. I was able to use the macro FindLastCell to get to the bottom of
the data. I also deleted all the "Active Window.ScrollRow" and "Active
Window.ScrollColumn" stuff, it really cleans up the code.

I still have issues with trying to get the Concatenate to stop at the last
row where cell "A" has data in it, as opposed to running down to row 5,000.
As well as figuring out code that will delete the filtered row with #N/A's
and copy the ones with #N/A's from the other worksheet and paste them under
the last row of data. I'll keep working on those issues an dhopefuly I can
figure something out.

Thanks again for the help you provided.

Chad

ryguy7272 said:
It would behoove you to delete all that 'ActiveWindow.ScrollRow' and
'ActiveWindow.ScrollColumn' stuff. It comes from recording a macro, and as
far as I can tell, It serves no practical purpose.

Find the end of a list:
here are a couple different ways:

this will select the cell. it's not a good practice to select, but just used
as
an illustration here.

range(worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address).Select

or if you just want A5 returned:

lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlup).address(0,0)

Find Last Used Cell:
Sub FindLastCell1()
Cells(Rows.Count, "A").End(xlUp).Select
End Sub

Sub FindLastCell2()
Range("A:A").Find("*", Cells(1), _
xlValues, xlWhole, xlByRows, xlPrevious).Select
End Sub

Since you are new to VBA in Excel, it may take a bit of work, and some
effort, to get a handle on this stuff, but you CAN do this. Just keep at it
and don't give up.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


CYaYa said:
Good afternoon,

I have set-up the following macro to do a number of tasks between two
worksheets in a workbook. It does what I need it to do, however I would like
to expand the capabilties of it and "fine tune" it abit.

Sheets("Current Unapplied").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Style = "Comma"
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Sheets("Unapplied Copy").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
ActiveWindow.SmallScroll ToRight:=3
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 958
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 411
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 138
ActiveWindow.ScrollRow = 1
Range("K2").Select
ActiveCell.FormulaR1C1 = "1"
Range("K3").Select
ActiveCell.FormulaR1C1 = "2"
Range("K4").Select
ActiveCell.FormulaR1C1 = "3"
Range("K2:K4").Select
Selection.AutoFill Destination:=Range("K2:K5000")
Range("K2:K5000").Select
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Current Unapplied'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Current Unapplied").Select
ActiveWindow.ScrollRow = 822
ActiveWindow.ScrollRow = 685
ActiveWindow.ScrollRow = 548
ActiveWindow.ScrollRow = 275
ActiveWindow.ScrollRow = 1
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-2],'Unapplied Copy'!R2C10:R5000C11,2,FALSE)"
Selection.AutoFill Destination:=Range("L2:L5000")
Range("L2:L5000").Select
Range("L1").Select
Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"
Sheets("Unapplied Copy").Select
Range("A1").Select
End Sub

The first issue I need help with is the following part of the macro (which
will also be used on the other parts that do the smae process):

Range("J2").Select
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(RC[-9],RC[-8],RC[-7],RC[-6],RC[-5],RC[-4],RC[-3])"
Selection.AutoFill Destination:=Range("J2:J5000"), Type:=xlFillDefault
Range("J2:J5000").Select

Is there any code I can use so instead of the range going to 5,000 the range
will stop where the data in cell A stops? I would also like to note that
every week the report varies in size (that is why I set the range to 5,000,
because I know the report will never be that large).

The next two issues deal with expanding the macro:

Sheets("Unapplied Copy").Select
ActiveSheet.Range("$A$1:$L$64999").AutoFilter Field:=12, Criteria1:="#N/A"

This part does what I need it to I would just like to expand the macro at
this point. I would like to insert code here to have the macro delete the
rows with #N/A, and then reset the filter on field 12 to ALL, then go to the
row below the row of last data in cell A.


Sheets("Current Unapplied").Select
ActiveSheet.Range("$A:$L").AutoFilter Field:=12, Criteria1:="#N/A"

Again this does what I need it to I would just like to expand it. I would
like to insert code to have the macro copy the rows with #N/A and paste them
below the last row of data on the sheet named "Unapplied Copy"

I greatly appreciate any help that can be provided.

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 3
Excel Macro delete certail column's 1
"=TEXT(RC[-1],"MMM")" 4
Creating Pivot Table with Macro using Multiple Sheets 0
Macro code 0
Macro Crashing 3
Vlookup in vba - how to use absolute rows not relative 1
vlookup from one workbook to another 0

Top