Using a macro to select multiple cells

[JohnnyC]

I'm trying to get a macro to add a row to the selection if the cell in column G has a zero in it. Is there any way of adding to the selection on a row-by-row basis like you can with sheets using the 'Replace' parameter?

 

[Tom Urtis]

Please clarify, do you mean if the cell contains a single "0", or the word "zero", or a number containing a 0, such as 1025?

 

[JohnnyC]

I mean the number zero, "0".

 

[Tom Urtis]

See if this macro does what you are requesting.

Sub AddZeroRow()
Application.ScreenUpdating = False
[G2].Select
Do Until Selection.Value = ""
If Selection.Value = "0" Then
Selection.Insert Shift:=xlDown
ActiveCell.Offset(2, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
[G1].Select
Application.ScreenUpdating = True
End Sub

Any help?

 

[JohnnyC]

Sorry, I obviously didn't make myself very clear. What I want to do is select multiple rows that are not next to each other, the equivalent of:

Range("9:9,12:12,14:14,19:19,23:23").Select

...where only rows with 0 in column G are selected. With sheets you can use

sheets.("MySheet").select(False)

to add to collection of selected sheets, but this doesn't work with range.select.

Is there anyway you can do this?

 

[Robb__]

Johnny

There may be an easier way, but try this:

Sub multiSelectRows()
Dim stmySelect() As String 'Array to store row numbers of found cells
Dim stLastSelect As String 'Variable to store last row number whilst comma stripped away
Dim stFullSelect As String 'Variable to hold full string of row numbers
Dim x As Integer
x = -1
With Worksheets("Sheet1")
For Each c In Columns(7).Cells
If c = "0" Then
x = x + 1
ReDim Preserve stmySelect(x)
stmySelect(x) = c.Row & ":" & c.Row & ","
Else
End If
Next c
stLastSelect = Left(stmySelect(x), Len(stmySelect(x)) - 1)
ReDim Preserve stmySelect(x - 1)
ReDim Preserve stmySelect(x)
stmySelect(x) = stLastSelect
stFullSelect = ""
For x = 0 To x
stFullSelect = stFullSelect & stmySelect(x)
Next x
.Range(stFullSelect).Select
.[A1].Activate
End With


End Sub

I don't know whether you need to check all the cells, if not just amend the range.

Any help?

Regards

Robb__

 

[JohnnyC]

That should do it. Thanks Robb.

You would have thought Excel would have made this easier to do...