CODEWITHSUNDEEP
vbaexcel
Archit Kacker
Archit Kacker

Reputation: 41

Excel Macro- How to delete cell values after a row number?

Columns A to K have entries till a specific number which may vary. Columns L to Q have entries till 1,50,000. I want to delete all entries after the last entry in Column A (this number is variable)

The code that I got after recording macro is given below.

The issue with the code is that it is hardcoding Application.Goto Reference:="R70086C1"

Sub ExtraDelete()
    ' ExtraDelete Macro
    Range("AN1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-39], ""*"")"
    Range("AN1").Select
    Selection.Copy
    Range("AN3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("AN1").Select
    Selection.Copy
    Application.Goto Reference:="R70086C1"
    Rows("70087:70087").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.ClearContents
    Range("A70082").Select
    ActiveWorkbook.Save
End Sub

Upvotes: 2

Views: 84

Answers (1)

user4039065
user4039065

Reputation:

I want to delete all entries after the last entry in Column A.

Typically, you would start at the bottom on column A and look up to find the last populated cell. It's like starting at A1048576 and tapping ctrl+<up arrow>.

Range.Select is almost never needed and rarely the best method. (read this)

Sub ExtraDelete()
    ' ExtraDelete Macro

    with worksheets("sheet1")
        .range(.cells(.rows.count, "A").end(xlup).offset(1, 0), _
               .cells(.rows.count, "A")).entirerow.clear
        .parent.Save
    end with

End Sub

Upvotes: 1

Related Questions