-1

I am a bit of a VBA novice so was hoping someone could help with the following. I need to select (and clear the contents of) all cells in a workbook before pasting new data in. Problem is when I use the (xlToLeft) command it stops when it encounters an empty cell.

I have the code below (which works) but wanted to see if there is a better way, which I am sure there will be.

    Sheets("TEST").Activate
    Range("BB3").Select
    Range(Selection, Selection.End(xlDown)).Select

    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range(Selection, Selection.End(xlToLeft)).Select

    Selection.ClearContents

Just for reference I have info in the cells to the right which I want to keep and the rows will change on each occasion I run the script.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
mhaverk
  • 57
  • 1
  • 7

3 Answers3

1

Why not clear all cells instead of looking for the ones with something in them?

Sheets("TEST").Activate
Activesheet.cells.ClearContents
Jeremy
  • 1,337
  • 3
  • 12
  • 26
  • Activating here is unnecessary (and should be avoided when possible) - https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba?rq=1 – Samuel Hulla Aug 13 '18 at 08:36
  • Hey Jeremy, I have some data in cells A1:BF2 and BB3:BF3 that i need to keep. Some headers and formulas so unfortunately can't clear all. – mhaverk Aug 13 '18 at 08:43
0

If you wish to clear a set range: 

Worksheets("Test1").Range("A1:I60").ClearContents

If you wish to clear a range based on a value in a row (* is a wildcard, you can substitute it for a text string if required): 

Dim lRow as Integer
lRow = Worksheets("Test1").Range("I:I").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Worksheets("Test1").Range("A1:I" & lRow).ClearContents

Best practice is to avoid using .Select/.Activate and any other indirect reference to a workbook/worksheet/range/cell and provide a fixed reference. Using any non-fixed reference slows down code execution but also makes it hard to review the code at a future date.

Clannagh
  • 133
  • 7
0

first things first - you should avoid using Select altogether.

It's a rather bad coding habit in VBA that's going to do you more harm than good in the long run. I'm not going to go into too much detail here, but I'd recommend reading (thoroughly) this question here:
How to avoid using Select in Excel VBA

I've created the following procedure:

clear_til_empty(ByVal inrow as Long)

Finds last non-empty cell in a specified row (inrow) and removes the cells in their respective columns.

Private Sub clear_til_empty(ByVal inrow As Long)
' clears all the cell contents until it hits an empty cell
  Dim ws As Worksheet: Set ws = Sheets("Sheet1")
  Dim lc As Long ' last empty column
  lc = ws.Cells(inrow, Columns.Count).End(xlToLeft).Column

  ' ws.Range("1:" & lc).ClearContents // didn't work as expected

  For i = 1 To lc
    ws.Columns(i).ClearContents
  Next i

End Sub

So for example, if we wanted to remove data from all columns, until an empty cell is hit in the first row, we would use the following invokation.

clear_til_empty(1)

Obviously, you can pass a variable to it instead.

Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
  • No problem, but more importantly,what about the actual answer. Does it work as intended?\ – Samuel Hulla Aug 13 '18 at 09:43
  • Thanks for the tip re .Select and the input on how best to solve my problem I went with the solution below because it was more simple for a novice like myself. – mhaverk Aug 13 '18 at 09:57