VBA macro to delete a row

Question

Hey i just created a macro added headers deleted info and got data formatted but i noticed that when i ran it for another file it just deleted the data in that exact cell i now need to do the same but delete the row that the phrase sits on

imagine i had a cell a1 in other versions of the document that phrase could be in a2 my macro would only delete whats in A1

the phrase is ZFD and whatever cell its in i need the macro to delete the entire row that phrase sits on HELPPPPP

Sub UMR()
'
' UMR Macro
'

'
 Range("A1").Select
ActiveCell.FormulaR1C1 = "Transaction_Type"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Meter_Point_Ref"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Actual_Read_Date"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading_Source"
Range("E1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading_Reason"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Meter_Serial_Number"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Meter_Reading"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Meter_ROC_Count"
Range("I1").Select
ActiveCell.FormulaR1C1 = "Meter_Read_Verified"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Corrector_serialNumber"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Corrector_serial_Number"
Range("K1").Select
ActiveCell.FormulaR1C1 = "Corrector_Uncorrected_Reading"
Range("L1").Select
ActiveCell.FormulaR1C1 = "Corrector_Corrected_Reading"
Range("M1").Select
ActiveCell.FormulaR1C1 = "Corrector_ROC_Count"
Range("N1").Select
ActiveCell.FormulaR1C1 = "Corrector_Usable_IND"
Range("O1").Select
ActiveCell.FormulaR1C1 = "Corrector_Read_Verified"
Range("A17").Select
Selection.ClearContents
Range("B17").Select
Selection.ClearContents
Columns("C:C").ColumnWidth = 8.29
Columns("C:C").EntireColumn.AutoFit
Columns("B:B").EntireColumn.AutoFit
Columns("A:A").EntireColumn.AutoFit
Columns("E:E").Select
Columns("D:D").EntireColumn.AutoFit
Columns("E:E").EntireColumn.AutoFit



Columns("F:F").EntireColumn.AutoFit
Columns("G:G").EntireColumn.AutoFit
Columns("H:H").EntireColumn.AutoFit
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
Columns("I:I").EntireColumn.AutoFit
Columns("J:J").EntireColumn.AutoFit
Range("Q1").Select
Columns("K:K").EntireColumn.AutoFit
Columns("L:L").EntireColumn.AutoFit
Range("R1").Select
Columns("M:M").EntireColumn.AutoFit
Columns("N:N").EntireColumn.AutoFit
Columns("O:O").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=6
ActiveWindow.SmallScroll ToRight:=-9
ActiveWindow.SmallScroll Down:=-88
End Sub

See [This](http://stackoverflow.com/questions/33744149/code-in-vba-loops-and-never-ends-how-to-fix-this) for three methods of deleting rows based on criteria. — Scott Craner, Feb 05 '16 at 16:13
Also, your entire macro can be reduced to maybe 10 lines or less. I highly recommend reading up on how to [avoid using `.Select`](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — BruceWayne, Feb 05 '16 at 17:47

score 0 · Answer 1 · answered Feb 09 '16 at 13:31

As I just did have the time I reorganized your code a little. Be aware that this is not commonly done here on stackoverflow. For next time: At least try to code something, if it's wrong that's not a problem, that's where we can help. And for your information: I am quite the newby as well (3,5 months of vba so far), so it's not that hard. Even if my code is not perfected yet, most of the time I can get it to work somehow...

Try this once (read the comments in the code first):

Sub UMR()

    Dim WS As worksheet
    Set WS = AcitveWorkbook.ActiveWorksheet 'be aware this will always be run on the activesheet

    Dim Values AS Variant
    Values = Array("Transaction_Type", "Meter_Point_Ref", "Actual_Read_Date", "Meter_Reading_Source", "Meter_Reading_Reason", "Meter_Serial_Number", "Meter_Reading", "Meter_ROC_Count", "Meter_Read_Verified", "Corrector_serialNumber", "Corrector_serial_Number", "Corrector_Uncorrected_Reading", "Corrector_Corrected_Reading", "Corrector_ROC_Count", "Corrector_Usable_IND", "Corrector_Read_Verified")
    Dim FindString As String
    FindString = "ZFD"

    Dim ZFDVal As Variant
    Dim IRow As Integer
    Dim ICol As Integer

    Set ZFDVal = Ws.Find(What:=FindString, _
                       After:=Ws.Cells(Ws.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _   'If the value is only a part of a cell it would be xlPart instead of xlWhole
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False) 'If you want it to Match the string exactly (regarding capital letters) you'll have to set this to true

    IRow = Range(ZFDVal.Adress).Row 'This is untested...

    For ICol = 1 To (UBound(Values)-LBound(Values))
        Ws.Cells(IRow, ICol) = Values(ICol-1)
    Next ICol

    Range("A17").Clear ' I believe this was unintendet and just recorded alongside so you can delete these two rows...
    Range("B17").Clear

    Columns("A:O").EntireColumn.AutoFit

End Sub

If you get a run-time error please press "debug" and comment which line gets marked yellow. This way we can help you correcting the code...

If the line ws.Cells(irow, icol) = values(icol-1) doesn't give you the right values into your cells, delete the "-1". — Kathara, Feb 09 '16 at 13:34

VBA macro to delete a row

1 Answers1