98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View Reviews
Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » A quick Macro
A quick Macro
Resolved · High Priority · Version 2003
Tue 24 Nov 2009, 08:57Delegate Gareth said...
Gareth has attended:
Excel VBA Intro Intermediate course
Excel Intermediate course
Excel Advanced course
A quick Macro
Hi
Im looking for a quick macro that will delete the entire contents of a row if one of the columns matches a certain criteria.
For example, in column U I have got an if function that will look for duplicates. The column will return a "Y" if the row is a duplicate.
What VB code will look for the "Y" in column U and delete all the rows?
Thanks in advance.
Wed 25 Nov 2009, 09:45Trainer Stephen said...
RE: A quick Macro
Hi Gareth
Thank you for your question
In general
First you will need to specify the column, probably through and inputbox
You will then need a loop that will run from the first row to the last. At each point there will be a conditional statement that checks to see if the contents of the cell = "Y". If they do then the key piece of code would be
Cells(intRowCount, 6).Columns.EntireColumn.DeleteRegards
Stephen
Thu 26 Nov 2009, 10:01Delegate Gareth said...
RE: A quick Macro
Hi Stephen thanks for your reply.
the column would stay as "U" always, would the input box be necessary to define the column or could this be written in the code?
Thanks
Thu 26 Nov 2009, 12:03Delegate Gareth said...
RE: A quick Macro
Hi Again
I have come across the following Code that seems to work well.
Sub DeleteMe()
Const strDelete As String = "Y"
Dim lLastRow As Long
Dim rng As Range
Application.ScreenUpdating = False
With ThisWorkbook.Worksheets("Bail List")
lLastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row
Set rng = .Range("H23", .Cells(lLastRow, "H"))
rng.AutoFilter field:=18, Criteria1:=strDelete
rng.SpecialCells(xlCellTypeVisible).EntireRow.Delete
.UsedRange
End With
Application.ScreenUpdating = True
If ActiveSheet.AutoFilterMode Then ActiveSheet.ShowAllData ' to remove the filter
End Sub
However, Once the row has been deleted it breaks the formulas and I get a lot of "#Ref" values in the cells. Is there a bit of code that will select the top cell. (will always be U23) and copy the formula down to the last row with data. I have used the macro recorder however this will get fixed at the last cell on that particular day. I need this code to be dynamic.
Thanks for your help.
Thu 26 Nov 2009, 13:50Trainer Stephen said...
RE: A quick Macro
Hi Gareth
Thanks for the update
I suggest something like
For i = 1 to range("U23").currentregion.rows.count
Range("u23").cells(i,1) = FORMULA
next i Where FORMULA is your formula
Regards
Stephen
Wed 2 Dec 2009: Automatically marked as resolved.
Training information:
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Jumping Across the Excel ScreenPgDn and PgUp keys scrolls up and down a screen page in most applications. |
