Using VBA to add active cell address to a range

Question

I'm trying to use this answer to apply my own sort of adding active cell address to a range so I can select the whole range later to a apply a conditional formatting. Below is my code but I'm not sure what is wrong. It came up with the error "Compile Error: Type Mismatch"

p/s: I have recorded my macro on this particular set of cells and then editted it.

Sub Macro1()

 Dim rng2 As Range

Range("B3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("D3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("F3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("H3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("J3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("L3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("N3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("P3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("R3").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("R7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("P7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("N7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("L7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("J7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("H7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("F7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("D7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("B7").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("B11").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("D11").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("F11").Select
Set rng2 = Union(rng2, ActiveCell.Address)
Range("J11").Select
Set rng2 = Union(rng2, ActiveCell.Address)



Range(rng2).Select
End Sub

score 1 · Accepted Answer · 2018-07-09T03:24:00.203

Your first Set rng2 = Union(rng2, ActiveCell.Address) should be Set rng2 = ActiveCell. You cannot union to a range object that is nothing and rng2 is nothing until it is set to something so it cannot be part of the union statement.
Each subsequent Set rng2 = Union(rng2, ActiveCell.Address) should be Set rng2 = Union(rng2, ActiveCell). You cannot union to a range object's string address; you must union to a range object.
rng2 is now a valid range object and does not require further definition. Range(rng2).Select is invalid; simply use rng2.Select.

For all intents and purposes, you could have just stated,

Range("B3, D3, F3, H3, J3, L3, N3, P3, R3, R7, P7, N7, L7, J7, H7, F7, D7, B7, B11, D11, F11, J11").Select

You mentioned in a comment that you cut off your code due to space restrictions. Consider the following loop.

dim i as long, rng2 as range

Set rng2 = Range("B3, D3, F3, H3, J3, L3, N3, P3, R3")

For i = 7 To 23 Step 4
    Set rng2 = Union(rng2, Intersect(rng2.EntireColumn, Rows(i)))
Next i

rng2.select
rng2.interior.color = vbred
Debug.Print rng2.Address(0, 0)
'B3,D3,F3,H3,J3,L3,N3,P3,R3,B7,D7,F7,H7,J7,L7,N7,P7,R7,B11,D11,F11,H11,J11,L11,N11,P11,R11,B15,D15,F15,H15,J15,L15,N15,P15,R15,B19,D19,F19,H19,J19,L19,N19,P19,R19,B23,D23,F23,H23,J23,L23,N23,P23,R23

The debug.print messages go to the VBE's Immediate window.

Thanks for the correction. It helped. As stated, i recorded this macro and actually reduced the coding lines to a few lines as stackoverflow only allowed me to show this much. There is alot of selected cells in the code which are recorded by macro for quick and adjustable works. Therefore, I dont have the time to reduce time to one line. I can use the 'replace all' function to do all the subsequent lines. Thank you very much, @Jeeped. — cokenol, Jul 09 '18 at 02:47
If you have a pattern, you could loop. In the above, you could loop between B and R, then inner loop through 3, 7, 11 as long as you exited when inner loop is 11 and column > J. (but that might just be a cutoff of code). — , Jul 09 '18 at 03:02
oh Thanks for the additional tip. I don't actually have a pattern. there are some imperfections somewhere along the line. Hence using the recorded macro. Otherwise, I would have just selected the whole rows. Thanks anyway man. Will explore further into VBA macro to further my knowledge. — cokenol, Jul 09 '18 at 03:45

Using VBA to add active cell address to a range

1 Answers1