Clear contents of cells in VBA using column reference

Question

I am trying to get a piece of code to clear the data in some cells  using the column references  I am using the following code   Worksheets sheetname  Range  Cells 2  LastColData    Cells LastRowData  LastColData   ClearContents   To do this  however I am getting an error at the first  Cells section  why is this

User · Answer

I just came up with this very simple method of clearing an entire sheet   Sub ClearThisSheet    ActiveSheet UsedRange ClearContents  End Sub

User · Answer

You can access entire column as a range using the Worksheet Columns object  Something like    Worksheets sheetname  Columns 1  ClearContents    should clear contents of A column  There is also the Worksheet Rows object if you need to do something similar for rows    The error you are receiving is likely due to a missing with block   You can read about with blocks here  Microsoft Help

User · Answer

The issue is not with the with statement  it is on the Range function  it doesn t accept the absolute cell value   it should be like Range  A4 B100     you can refer the following thread for reference     following code should work   Convert cells 1 1  into  quot A1 quot  and vice versa  LastColData   Sheets WSNAME  Range  A4   End xlToRight  Column             LastRowData   Sheets WSNAME  Range  A4   End xlDown  Row             Rng    A4    amp  Sheets WSNAME  Cells LastRowData  LastColData  Address RowAbsolute  False  ColumnAbsolute  False    Worksheets WSNAME  Range Rng  ClearContents

User · Answer

I found this an easy way of cleaning in a shape between the desired row and column  I am not sure if this is what you are looking for  Hope it helps   Sub sbClearCellsOnlyData   Range  A1 C10   ClearContents End Sub

User · Answer

As Gary s Student mentioned  you would need to remove the dot before Cells to make the code work as you originally wrote it   I can t be sure  since you only included the one line of code  but the error you got when you deleted the dots might have something to do with how you defined your variables     I ran your line of code with the variables defined as integers and it worked   Sub TestClearLastColumn        Dim LastColData As Long         Set LastColData   Range  A1   End xlToRight  Column      Dim LastRowData As Long         Set LastRowData   Range  A1   End xlDown  Row      Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData   ClearContents  End Sub   I don t think a With statement is appropriate to the line of code you shared  but if you were to use one  the With would be at the start of the line that defines the object you are manipulating  Here is your code rewritten using an unnecessary With statement   With Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData        ClearContents End With   With statements are designed to save you from retyping code and to make your coding easier to read   It becomes useful and appropriate if you do more than one thing with an object  For example  if you wanted to also turn the column red and add a thick black border  you might use a With statement like this   With Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData        ClearContents      Interior Color   vbRed      BorderAround Color  vbBlack  Weight  xlThick End With   Otherwise you would have to declare the range for each action or property  like this       Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData   ClearContents     Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData   Interior Color   vbRed     Worksheets  Sheet1   Range Cells 2  LastColData   Cells LastRowData  LastColData   BorderAround Color  vbBlack  Weight  xlThick   I hope this gives you a sense for why Gary s Student believed the compiler might be expecting a With  even though it was inappropriate  and how and when a With can be useful in your code

User · Answer

To clear all rows that have data I use two variables like this  I like this because you can adjust it to a certain range of columns if you need to      Dim CRow As Integer     Dim LastRow As Integer  CRow   1 LastRow   Cells Rows Count  3  End xlUp  Row  Do Until CRow   LastRow   1     Cells CRow  1  Value   Empty     Cells CRow  2  Value   Empty     Cells CRow  3  Value   Empty     Cells CRow  4  Value   Empty     CRow   CRow   1 Loop

User · Answer

For anyone like me who came across this and needs a solution that doesn t clear headers  here is the one liner that works for me   ActiveSheet Range  A3 A   amp  Range  A3   End xlDown  Row  ClearContents   Starts on the third row - change to your liking

User · Answer

You need a With statement prior to this   Or make the  Cells into Cells

[vba] Clear contents of cells in VBA using column reference

Examples related to vba

Examples related to excel