dabug80
asked on
Excel Macro Fill Down and Delete Rows
Hi,
I would like to my Excel below macro to do the following:
1. Select three cells and enter a respective formula (I think I've captured this)
2. Select these three cells as a range and automatically fill down the formula as far as the last populated cell in column F (there are no blanks in between)
3. Find all the rows from row 7, where column P = "Yes" and delete those rows
What's the best way of doing this? Here's my current approach:
I would like to my Excel below macro to do the following:
1. Select three cells and enter a respective formula (I think I've captured this)
2. Select these three cells as a range and automatically fill down the formula as far as the last populated cell in column F (there are no blanks in between)
3. Find all the rows from row 7, where column P = "Yes" and delete those rows
What's the best way of doing this? Here's my current approach:
Sub mac_addressformat()
'
' mac_addressformat Macro
'
Dim lastrow As Long
lastrow = Range("F6500000").End(xlUp).Row
'
Range("P7").Select
ActiveCell.FormulaR1C1 = "=IF(LEFT(RC[-1],3)=""Vic"",""Yes"",""Delete"")"
Range("Q7").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-2],7,SEARCH("","",RC[-2],1)-7)"
Range("R7").Select
ActiveCell.FormulaR1C1 = "=RIGHT(RC[-3],4)+0"
Range("P7:R7").Select
Selection.AutoFill Destination:=Range("P7:R7" & lastrow)
End Sub
can you send me a subset of your excel sheet? Sheet1 would have sample raw data and Sheet2 would have the results you want.
ASKER
Sure. Here's a sample Sample.xlsm
Thanks. So you just want to delete all quotes that are non vic correct? I assume you want a marco so you can repeat this?
ASKER
Yes please. Actually - I want to delete all the rows with 'delete' in them
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
@dabug80
Rgonzo's code will probably work too but mine has less code/operations as it just deletes the rows directly rather than mark them first for deletion.
Rgonzo's code will probably work too but mine has less code/operations as it just deletes the rows directly rather than mark them first for deletion.
ASKER
Thanks both. Cheers for your efforts prequel_server. I've split the points.
HI,
the code of PrequelServer is wrong!
if you have two consecutive lines where the zone is not Vic it will only delete the first one as by deleting the line the second one becomes the first line of the two, that's why by deleting you have to begin from the bottom or adjust the index when deleting.
Regards
the code of PrequelServer is wrong!
if you have two consecutive lines where the zone is not Vic it will only delete the first one as by deleting the line the second one becomes the first line of the two, that's why by deleting you have to begin from the bottom or adjust the index when deleting.
Regards
ASKER
Ok. Thanks for the clarification Rgonzo.
it's not exactly "wrong" it just doesn't account for that case. Running the code twice on your data takes care of that.
Also depending on how much data you have, running either code is slow b/c it the row delete operation is taxing. What you might want to do before running either code is sort on the Zone column, this way all of non-Vics are brought together before mass deletion. You can do this manually in excel or in VBA. Just record a macro of yourself sorting manually and you'll see the code behind it.
good luck
Also depending on how much data you have, running either code is slow b/c it the row delete operation is taxing. What you might want to do before running either code is sort on the Zone column, this way all of non-Vics are brought together before mass deletion. You can do this manually in excel or in VBA. Just record a macro of yourself sorting manually and you'll see the code behind it.
good luck
ASKER
Cheers Prequel
Note on prequel code
if you have 16 lines to delete and you've sorted the data, you will have to run the macro 5 times to delete all the lines
First time deleting 8 lines, 2nd time 4, 3rd time 2, 4th time 1 and last time 1 line
Regards
if you have 16 lines to delete and you've sorted the data, you will have to run the macro 5 times to delete all the lines
First time deleting 8 lines, 2nd time 4, 3rd time 2, 4th time 1 and last time 1 line
Regards
ASKER
Ok all. FYI - I settled on using Rgonzo code.