Solved

Excel Macro Fill Down and Delete Rows

Posted on 2014-09-02
14
274 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
  • 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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
LVL 1

Author Comment

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

Accepted Solution

by:
Rgonzo1971 earned 350 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 150 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 49

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 49

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel Conditional Median Problem 1 15
Best prediction based on two lists of numbers  in excel 2 41
And OR formula 5 22
sumifs excel 2013 3 14
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

770 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