Selecting cells in the same row in various columns forClearingContents

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

(This is a portion of my macro which loops through multiple sheets,
each with various rows of data to test)

Sub OpenSheet1() [ Sheet has a Name]
'Opens Sheet 1 for clearing sold out items
Worksheets("Sheet1").Activate
Range("M2").Activate
Dim rwindex As Integer [rwindex is row
number]
For rwindex = 2 To Range("data") + 1 [ "data" is number of
items in the sheet. Row "rwindex", starts at 2)
rwindex = rwindex
If Cells(rwindex, 13).Value = 0 Then [This tests the cell
value down rows, COL 13, (M), constant]
Range("rwindex, 1").Activate [THIS is my
DILEMMA]????????
ActiveCell.Select
Selection.ClearContents

My loop works until Value 0 is found, then this fails.
I need to select the cell in ("rwindex, 1")..COL A. There are other
cells in rwindex to clear also.

I've tried to use Range (Array of 9 contiguous cells, then 3 other
non-contiguous, in order to clear contents. Macro Recording used
cell addresses, A5, B5, etc. I don't have that info.
My Arrays did not function, so, the above macro selects one cell at a
time. I can live with that.

HOWEVER:
Range("rwindex, 1").Activate (Or Select) This does not work for
selecting "rwindex, 1" as Col 1, (A ,etc)
As this is looping Col M, (13), I have no way of knowing in which row
a cell value is 0, and how to select the cells to clear.

I've used Scrolling, down, left, right, and have not found a way to
Select or Activate the cell, for it's Value.

Thanks for any help. I've searched for 2-3 weeks for a solution.

Ed
 
try something like this where you do NOT select anything including the
sheet.

With sheets("sheet1").Range("m1:m100")
Set c = .Find(0, lookin:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.clearcontents
c.offset(,2).clearcontents
'etc
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
 
Back
Top