Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes and pastes filterd rows and then some
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes and pastes filterd rows and then some
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").e nd(xlup).address).Select or if you just want A5 returned: lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlu p).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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro that deletes and pastes filterd rows and then some
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" wrote: 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").e nd(xlup).address).Select or if you just want A5 returned: lastcell = worksheets("Sheet1").cells(rows.Count,"A").end(xlu p).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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hidden rows reappearinmg when macro deletes rows | Excel Programming | |||
Macro That Deletes Rows Extremely Slow | Excel Programming | |||
Macro that Deletes All rows except first row | Excel Programming | |||
Macro that deletes certain rows only | Excel Programming | |||
Macro that deletes certain rows and not others | Excel Programming |