Relative Cell References in Macros

  • Thread starter Thread starter Barry Staples
  • Start date Start date
B

Barry Staples

Relative cell References within a LOOP and IF Statement


I’m new to writing macros for Excel 97
I wish to clear a range of cells on the current line to the left of th
active cell.
I’m having trouble with setting up a “range” with relative cel
values.
The following routine works well to clear an individual cell,
How do I modify it to clear a range of cells.

Any help would be really appreciated


Sub Loop1()
' This loop runs down a column until there is nothing in the column o
the it’s immediate left
‘ Clears a cell 5 columns to left

Do
ActiveCell.Select
If ActiveCell.Offset(0, -1) = 0 Then Clear_Cells
ActiveCell.Offset(1, 0).Select
‘ gets next row
Loop Until IsEmpty(ActiveCell.Offset(0, -1))
End Sub

Sub Clear_Cells()
ActiveCell.Offset(0, -5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Barry Staples Nottingham Englan
 
Sub Clear_Cells()
ActiveCell.Offset(0, -5).Resize(1,5).Select
Selection.ClearContents
ActiveCell.Offset(0, 5).Select
End Sub

Or, remove the Select:


Sub Clear_Cells()
ActiveCell.Offset(0, -5).Resize(1,5).ClearContents
End Sub
 
Barry,

Sub test()
With ActiveCell
If .Column > 1 Then
.Offset(0, -1).Resize(1, Columns.Count - .Column +
2).ClearContents
End If
End With
End Sub


Rob
 
Back
Top