CODEWITHSUNDEEP
excelvba
Archana
Archana

Reputation: 1

I have a VBA Query

I am working on a VBA code to find the last row in an excel sheet and delete the rows above till we reach the headers on row1.
I am continuously getting a compile error with end(XLUP)
Here is my code:

Dim rw As Integer
Range("A1048576").End(xlup).Select
rw = ActiveCell.Row
Cells.Select
Range("A" & rw).Activate
Selection.DELETE Shift:=xlup
Range("A1").Select

Upvotes: 0

Views: 35

Answers (3)

DisplayName
DisplayName

Reputation: 13386

If your column A values are contiguous (i.e. doesn't have blanks in between) then Jeeped solution is the way to go

otherwise you may want to use:

With Cells(Rows.Count, 1).End(xlUp) 'reference last not empty cell in column A
    If .Row > 1 Then .Offset(-.Row + 2).Resize(.Row - 1).EntireRow.Delete xlUp
End With

in both cases, you can choose between following methods to act on wanted range:

  • .ClearContents (to clear the range content only, preserving formatting and notes)

  • .Clear (to clear the whole content of the range, formats and notes included)

  • .Delete (to remove the range, it has the same effect of .Clear plus rows shifting)

the methods above are listed from the less invasive (mainly, time consuming) to the most one

Upvotes: 0

user4039065
user4039065

Reputation:

Try,

with worksheets("sheet1")
    .cells(1, 1).currentregion.offset(1, 0).clear
end with

Upvotes: 0

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9966

Declare rw As Long

You may try this to clear the cells in column A except the header row1.

Dim rw As Long
rw = Cells(Rows.Count, "A").End(xlUp).Row
If rw > 1 Then Range("A2:A" & rw).Clear

Upvotes: 1

Related Questions