Range(Cells(activeCell.row,1),ActiveCell.offset(0,-1)).ClearContents

--
Regards
Tom Ogilvy


Barry Staples wrote in
message ...
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 the
active cell.
I'm having trouble with setting up a "range" with relative cell
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 on
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 England


---
Message posted from http://www.ExcelForum.com/