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

Question

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

It's not quite clear what you're trying to do. Are you trying to delete all the rows below the last value in Column A? — Olly, Sep 20 '18 at 09:42
Yes. 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. — Archit Kacker, Sep 20 '18 at 09:51

score 1 · Answer 1 · 2018-09-20T10:28:17.763

1

_{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

edited Sep 20 '18 at 10:28

answered Sep 20 '18 at 09:59

The .offset(1,0) moves down one row from the last populated cell in column A. – Sep 20 '18 at 10:04
I want to delete from the last entry in Column A (lets call it X) AX: Q150000 Will the code be able to do that? – Archit Kacker Sep 20 '18 at 10:04
No worries, that was a potentially a big error. You should probably test code you get from the internet on a copy of the original. – Sep 20 '18 at 10:26
Alright. I have another issue. Please be kind enough to resolve this as well. – Archit Kacker Sep 20 '18 at 10:27
The below code does not work. I am trying to create a pivot with average of Hrs and I don't want the value 0 to be considered in the average. With ActiveSheet.PivotTables("AnalyticsPivotTable").PivotFields("Hrs") .Orientation = xlDataField .Position = 2 .Function = "=Average If(M:M,"">0"")" .NumberFormat = "#,##0" – Archit Kacker Sep 20 '18 at 10:28
1

That would be a new question with new sample data, expected results and (hopefully) showing some original effort. – Sep 20 '18 at 10:29
I have to wait another 90 mins apparently to ask a question. I am new to this site and therefore do not know the way around. But I needed the answer so thought I'll ask you – Archit Kacker Sep 20 '18 at 10:31
Well, I could answer slower in the future so you don't have to wait so long :) Why don't you get started collecting up everything you need so you will be prepared to submit a great question? – Sep 20 '18 at 10:33

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

1 Answers1