Solved

Excel Macro Fill Down and Delete Rows

Posted on 2014-09-02
14
273 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 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel ODBC connection error 3 31
Hiding column macro 10 28
DBF to ... Converter 5 32
MS Excel IF AND OR statement 3 26
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…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 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

25 Experts available now in Live!

Get 1:1 Help Now