Solved

Excel Macro Fill Down and Delete Rows

Posted on 2014-09-02
14
272 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
 
LVL 1

Author Comment

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

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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 1

Author Closing Comment

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

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 48

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now