0

I am trying to clear a data in a worksheet in excel using vba. I want to clear the cells with data in them, not including Row 1. I am trying to get the variable of the last row and column with data but I keep getting an out of range error.

Dim lRow As Long
Dim lCol As Long
lRow = Sheets("Sheet1").Cells(Sheets("Sheet1".Rows.Count,1).End(xlUp).Row
lCol = Sheets("Sheet1").Cells(1, Sheets("Sheet1").Columns.Count).End(xlToLeft).Column

I want to use this logic to replace what I have hard coded:

Sheets("Sheet1").Range("A2:D1000").ClearContents

I am using multiple sheets so that's why I am specifying Sheet1. How can I do this?

Thanks.

Carly
  • 17
  • 5
  • 1
    "I am using multiple sheets so that's why I am specifying Sheet1" - can you explain what you mean by this? If you don't have a sheet named `"Sheet1"`, then you'll get a subscript out of range error. – BigBen Jul 26 '21 at 13:06
  • `Cells(Sheets("Sheet1".Rows.Count` should be `Cells(Sheets("Sheet1").Rows.Count` Or skip the sheet call in `.rows.count` because all sheets have the same row count. – Warcupine Jul 26 '21 at 13:14
  • @BigBen I mean that I have Sheet1, Sheet2, and Sheet3 and therefore put in "Sheet1" to specify I want to access the range in that sheet – Carly Jul 26 '21 at 14:39
  • If you're getting a subscript out of range error, then the active workbook does not have a sheet named `"Sheet1"`. – BigBen Jul 26 '21 at 14:42

2 Answers2

0
Dim ws As Worksheet
set ws = Sheets ("Sheet1")
ws.Range(ws.Cells(2,1),ws.Cells(ws.UsedRange.Rows.Count,1).EntireRow.Delete

should do the trick. UsedRange tracks the smallest rectangle in the worksheet containing all cells with data and starting from A1.

Chris Maurer
  • 2,339
  • 1
  • 9
  • 8
  • 2
    Please note that the `Cells` calls need to be qualified with `ws` as well, otherwise this is prone to blow up (see [this](https://stackoverflow.com/questions/8047943/excel-vba-getting-range-from-an-inactive-sheet) for example). Also the assumption that `UsedRange` begins in A1 is not always correct. For example, enter "foo" in cells `D2:D10`, and then in the Immediate Window use `? ActiveSheet.UsedRange.Address` ... returns `$D$2:$D$10`. – BigBen Jul 26 '21 at 13:46
0

You were missing a Parenthesis in the last row line you do not need to find the last column unless you are limiting the clear contents to preserve data.

One more thing, you are finding the last row in column A only, so if there is data in another column lower that the last one in column A, you won't clear that data.

 lRow = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count,1).End(xlUp).Row
    Sheets("Sheet1").Rows("2:" & lRow).ClearContents
mooseman
  • 1,997
  • 2
  • 17
  • 29
  • I tried doing `Sheets("Sheet1").Rows("A2:" & lRow).ClearContents` instead but it says there is a type mismatch – Carly Jul 26 '21 at 14:51
  • Actually, this only clears the 1st and 2nd row :( – Carly Jul 26 '21 at 15:04
  • This cleared the data of sheet with over 500 rows of data without clearing the 1st row. If you are trying Sheets("Sheet1").Rows("A2:" & lRow).ClearContents, it needs to have the end column like Sheets("Sheet1").Rows("A2:D" & lRow).ClearContents Are you sure there is continuous data in column A? Use F8 to step through the code and see what the value for lRow is. – mooseman Jul 26 '21 at 16:02