?
Solved

Excel Macro Fill Down and Delete Rows

Posted on 2014-09-02
14
Medium Priority
?
278 Views
Last Modified: 2014-09-04
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:

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

Open in new window

0
Comment
Question by:dabug80
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
14 Comments
 
LVL 3

Expert Comment

by:prequel_server
ID: 40300214
can you send me a subset of your excel sheet? Sheet1 would have sample raw data and Sheet2 would have the results you want.
0
 
LVL 1

Author Comment

by:dabug80
ID: 40300225
Sure. Here's a sample Sample.xlsm
0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40300241
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?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:dabug80
ID: 40300252
Yes please. Actually - I want to delete all the rows with 'delete' in them
0
 
LVL 52

Accepted Solution

by:
Rgonzo1971 earned 1400 total points
ID: 40300271
Hi,

pls try

Sub mac_addressformat()
'
' mac_addressformat Macro
'
Dim lastrow As Long

lastrow = Range("F" & Rows.Count).End(xlUp).Row
'
Range("P7").FormulaR1C1 = "=IF(LEFT(RC[-1],3)=""Vic"",""Yes"",""Delete"")"
Range("Q7").FormulaR1C1 = "=MID(RC[-2],7,SEARCH("","",RC[-2],1)-7)"
Range("R7").FormulaR1C1 = "=RIGHT(RC[-3],4)+0"
Range("P7:R7").AutoFill Destination:=Range("P7:R" & lastrow)
    
For Idx = lastrow To 7 Step -1
    If Range("P" & Idx).Value = "Delete" Then
        Range("P" & Idx).EntireRow.Delete
    End If
Next
End Sub

Open in new window

Regards
0
 
LVL 3

Assisted Solution

by:prequel_server
prequel_server earned 600 total points
ID: 40300278
this should work

Sub removenonvic()

'get number of rows
N = WorksheetFunction.CountA(Columns(6))

'loop through all rows
For i = 0 To N

    'Delete the row if it's not a vic
   If Left(Cells(7 + i, "O"), 3) <> "Vic" Then
 
       Rows(7 + i).Delete
 
   End If
 
Next i

End Sub

Open in new window

0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40300281
@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.
0
 
LVL 1

Author Closing Comment

by:dabug80
ID: 40300291
Thanks both. Cheers for your efforts prequel_server. I've split the points.
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40300311
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
0
 
LVL 1

Author Comment

by:dabug80
ID: 40300341
Ok. Thanks for the clarification Rgonzo.
0
 
LVL 3

Expert Comment

by:prequel_server
ID: 40301121
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
0
 
LVL 1

Author Comment

by:dabug80
ID: 40302777
Cheers Prequel
0
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 40302791
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
0
 
LVL 1

Author Comment

by:dabug80
ID: 40302813
Ok all. FYI - I settled on using Rgonzo code.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question