Identify Blanks; All Listed in Column AX

[ryguy7272]

I am trying to find all blanks in my used range; my used range which goes
from E3:W330 and from Y3:AE330. I keep getting results like $AG$1, $AH$1,
$AI$1, $AJ$1, all listed in column AX (vertically). I am wondering how to
tell Excel just to look in E3:W330 and in Y3:AE330. Also, I am interested in
getting results in each row, such as $E$3, $F$3, $G$3 (all in one cell or in
one cell and then offset 0,1), if these cells are blank, and when all blanks
are found, shift down one row (offset 1,0) then identify any blanks in that
row. Does this make any sense? Any assistance would be sincerely
appreciated!

The code that I am using now is listed below:

Sub FindBlanks()

Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngBlanks = Cells.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

If Not rngBlanks Is Nothing Then
'Worksheets.Add.Name = "Summary"
Set rngToPaste = Sheets("15.1 Detail - Madeup").Range("AX3")
For Each rng In rngBlanks
rngToPaste.Value = rng.Address
Set rngToPaste = rngToPaste.Offset(1, 0)
Next rng
End If
End Sub


This is just a sample; it doesn't really do what I want (as described above)

Kind Regards,
Ryan---

 

[Bill Renaud]

To find blanks in a range that is a portion of an entire worksheet,
first define a range that is the subset of cells, then apply the
SpecialCells method to that range. Revise the first few lines of your
code like so:

Public Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range


On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0
...
...

End Sub

I am not sure about the second part of your question.

 

[Gary Keramidas]

this may give you and idea:

Sub test()
Dim rng1 As Range
Dim rng2 As Range
Dim cell As Range
Set rng1 = Range("E3:w330")
Set rng2 = Range("Y3:Ae330")

With Union(rng1, rng2)
.SpecialCells(xlCellTypeBlanks).Select
End With
For Each cell In Selection
Debug.Print cell.Address ' these will show in the immediate window
Next

End Sub

 

[ryguy7272]

Thanks Bill and Gary! I think I’m getting close, but I'm not quite there
yet. Below is the code that I have now, but it is still not working…

Sub FindBlanks()
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rngMyRange As Range
Dim rng As Range


On Error Resume Next
Set rngBlanks = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngToPaste = rngMyRange.SpecialCells(xlCellTypeBlanks)

On Error GoTo 0

If Not rngBlanks Is Nothing Then
'Worksheets.Add.Name = "Summary"
Set rngToPaste = Sheets("15.1 Detail - Madeup").Range("AX3")
For Each rng In rngBlanks
rngToPaste.Value = rng.Address
Set rngToPaste = rngToPaste.Offset(1, 0)
Next rng
End If
End Sub

This code yields the following: $E$3, $F$3, $G$3, $H$3, $I$3, $J$3, etc.,
etc., etc. For some reason, the ‘Union’ doesn’t seem to be working. I
created a function, and found that there is one blank in this range, and it
happens to be in $F$3. Also, the offset 0,1 for each blank isn’t in the code
yet, and offset 1,0 isn’t working correctly either. I’m going to devote a
little time to this in the AM. If someone has and ideas, please share.

Regards,
Ryan---

 

[ryguy7272]

I finally got it working (code below):

Sub FindBlanks()
Dim rngMyRange As Range
Dim rngBlanks As Range
Dim rngToPaste As Range
Dim rng As Range

On Error Resume Next
Set rngMyRange = Union(Range("E3:W330"), Range("Y3:AE330"))
Set rngBlanks = rngMyRange.SpecialCells(xlCellTypeBlanks)
On Error GoTo 0

Set rngToPaste = rngBlanks
If Not rngBlanks Is Nothing Then
Set rngToPaste = Sheets("15.1 Detail - Madeup").Range("AX3")
For Each rng In rngBlanks
rngToPaste.Value = rng.Address
Set rngToPaste = rngToPaste.Offset(1, 0)
Next rng
End If
End Sub

It pretty much does what I want, without the offset (0,1)
Thanks for the assist Bill and Gary.

 

[Bill Renaud]

I don't think you need the line:

Set rngToPaste = rngBlanks

right above the If statement, since it is reset to "= Sheets("15.1
Detail - Madeup").Range("AX3")" inside the If statement.

 

[ryguy7272]

Bill, you are correct. I tried several things and I thought this line of
code was key to making the sub work. However, I just 'ticked' out that line,
and now it works just as it did before it ‘ticked’ it out. I think I'm going
to have to study this a little closer.

Thanks for pointing that out to me.

Regards,
Ryan---