Advice how to edit values in non-contig. range

[YellowLabPro]

I want to change all values that are not 0:00 to 0:00. The range is R2:BS72. From the graphic you will see it is every third column and two rows, skip row, 2 rows, etc...
I would like advice how to approach this, either a search and find or a loop through each.

thanks

YLP

[lucas]

Looking for a reset of the sheet....like for starting over..?

[YellowLabPro]

exactly

[lucas]

Hi Yelp,
This will get you started....you will have to add the other columns to the union(Range........but it works for me. Not sure if there is a better way.
[vba]Sub a()
Dim CurCell As Object
For Each CurCell In Union(Range("R2:R72"), Range("U:U"))
If CurCell.Value > 0 Then CurCell.Value = "0:00"
Next
End Sub[/vba]

[lucas]

doh....more precicely:
[VBA]Sub a()
Dim CurCell As Object
For Each CurCell In Union(Range("R2:R72"), Range("U2:U72"))
If CurCell.Value > 0 Then CurCell.Value = "0:00"
Next
End Sub[/VBA]

[YellowLabPro]

Steve,
Worked beautifully, :-) , thanks.
But if you don't mind...
Why Union?

[Bob Phillips]

Because it isn't contiguous.

[johnske]

You can also omit union and just use the range object for non-contiguous ranges...
[VBA]
Option Explicit
'
Sub a()
Dim CurCell As Object
For Each CurCell In Range("R2:R72, U2:U72")
If CurCell.Value > 0 Then CurCell.Value = "0:00"
Next
End Sub
[/VBA]

[YellowLabPro]

Instead of naming each range, since it is a consistent structure, is there some way to to qualify an offset, x number of times if R2:R72 is the first range and it has an offset of 3 columns and if it needs to repeat 20 times?

[mdmackillop]

If your bracketed figures are not numerical constants, you could try
[VBA]Range("R2:BS72").SpecialCells(xlCellTypeConstants, 1).Value = "0:00"[/VBA]

[YellowLabPro]

MD,
Could you elaborate or explain what you mean by bracketed figures or numerical constants please?

BTW: Your solution was dead-on, I tried it after posting. I would like to understand the solution....

Thanks,

Doug

Thanks also Steve and Johnske, these two solutions helped not only w/ the particular result but another little piece to the pie....

[mdmackillop]

Hi Doug
In a speadsheet press Function Key F5, Click Special, and select Constants. You'll see the available choices.
Re brackets, your screenshot shows (1), (2) etc. These could be custom formatted numbers, which would be changed by the SpecialCells method, or Text, which would not.

[YellowLabPro]

Thanks MD,
That is really helpful. And really dumb luck on my part, the bracketed values are text, not sure why I set those up as text as I set this up a couple of years ago. Messing w/ the specialcells also helps uncover more excel knowledge, thanks.
There is still a lingering question, Steve's, Johnske's and your solution definitely work. But I see a pattern in my worksheet and if I what I see and if it is possible, this could have other uses-

The names going across in row one, which begin in Q,T,W, Z... which are every other two rows, are constants. These names are repeated in Column A, beginning in A2 and contiguous, down. I changed them from constants a while back to =Q1, =T1, =W1, etc...
But I wonder if possible w/ the names in (A) could be found by a formula using offset.
This is not correct, but the idea I have is something like this-
=OFFSET(Q$1,0,0) and the cell reference would need to be relative and somehow be instructed to increase by 2, or the offset reference would need to increase by two columns.
I might be overstretching the boundaries here, but at least like to explore it. Thanks for the help.

Doug

[Paul_Hossler]

Another way to do it would be to do something like this. I try to stay away from VBA loops, just for perfomrance reasons, since this is VBA/Excel and not a stand-alone EXE. I figure that if Bill Gate$ was nice enought to put all that power in Excel, I might as well use it.

This seems to work since your data / spreadsheet format was very regular and consistant. It just does some Range intersections to select the data that you want to make 0:00.

Several lines are in for debug/validation, and several could be conbined

HIH, Paul


[vba]
Sub aaa()
Dim rAll As Range
Dim rBlankRow As Range
Dim rFinal As Range

Set rAll = Worksheets("Sheet1").Range("A1").CurrentRegion
Set rBlankRow = Intersect(rAll, rAll.Columns(1).SpecialCells(xlCellTypeBlanks)).EntireRow
Set rFinal = rBlankRow.SpecialCells(xlCellTypeConstants)

rFinal.Select
MsgBox Selection.Address
rFinal.Value = "0:00"
End Sub

[/vba]