Hello Experts! I used the macro recorder to find and color all blank cells
in a used range in my worksheet. It is easy to do by firing up the recorder,
hitting Ctrl + g, ckicking €˜Special, selecting Blanks, and filling in the
blanks with a color. I am wondering if there is a way to do something
similar, but identify the blanks cells by each respective cell address, and
put all of these in some kind of summary sheet. For instance, clicking the
commandbutton would result in a €œSummary€� sheet being created and all blank
cells would be listed by cells address, such as $D$5, $F$14, $J$92...if these
cells are blank.

Any ideas?

Kind Regards,
Ryan---

Dim r as Range, cell as Range
Dim rw as Long
On Error resume Next
set r = cells.Specialcells(xlBlanks)
On error goto 0
if not r is nothing then
for each cell in r
rw = rw + 1
worksheets("Sheet2").Cells(rw,1).Value = cell.Address
Next
End if

this assumes you don't have more than 8192 discontiguous areas of blank
cells.

--
Regards,
Tom Ogilvy

"ryguy7272" wrote:

Hello Experts! I used the macro recorder to find and color all blank cells
in a used range in my worksheet. It is easy to do by firing up the recorder,
hitting Ctrl + g, ckicking €˜Special, selecting Blanks, and filling in the
blanks with a color. I am wondering if there is a way to do something
similar, but identify the blanks cells by each respective cell address, and
put all of these in some kind of summary sheet. For instance, clicking the
commandbutton would result in a €œSummary€� sheet being created and all blank
cells would be listed by cells address, such as $D$5, $F$14, $J$92...if these
cells are blank.

Any ideas?

Kind Regards,
Ryan---

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
Set rngToPaste = Sheets("Summary").Range("A2")
For Each rng In rngBlanks
rngToPaste.Value = rng.Address
Set rngToPaste = rngToPaste.Offset(1, 0)
Next rng
End If
End Sub
--
HTH...

Jim Thomlinson


"ryguy7272" wrote:

Hello Experts! I used the macro recorder to find and color all blank cells
in a used range in my worksheet. It is easy to do by firing up the recorder,
hitting Ctrl + g, ckicking €˜Special, selecting Blanks, and filling in the
blanks with a color. I am wondering if there is a way to do something
similar, but identify the blanks cells by each respective cell address, and
put all of these in some kind of summary sheet. For instance, clicking the
commandbutton would result in a €œSummary€� sheet being created and all blank
cells would be listed by cells address, such as $D$5, $F$14, $J$92...if these
cells are blank.

Any ideas?

Kind Regards,
Ryan---

I'm not sure if there is a function that converts column numbers to letters,
so I had to add the code myself. the code would be much simplier if the
columns appear as lettters. is assumed you want column lettters?

Sub test()

RowCount = 1
first = True
For Each cell In Selection
If IsEmpty(cell) Then
If first = True Then
Worksheets.Add
ActiveSheet.Name = "Summary"
first = False
End If
ColNumber = cell.Column
Col1stNumber = Int((ColNumber - 1) / 26)
Col2ndNumber = (ColNumber - 1) Mod 26
ColLettter = Chr(Asc("A") + Col2ndNumber)
If Col1stNumber < 0 Then
ColLettter = Chr(Asc("A") + (Col1stNumber - 1)) & _
ColLettter
Else

End If
Sheets("Summary").Cells(RowCount, "A") = _
ColLettter & cell.Row
RowCount = RowCount + 1
End If
Next cell

End Sub

"ryguy7272" wrote:

Hello Experts! I used the macro recorder to find and color all blank cells
in a used range in my worksheet. It is easy to do by firing up the recorder,
hitting Ctrl + g, ckicking €˜Special, selecting Blanks, and filling in the
blanks with a color. I am wondering if there is a way to do something
similar, but identify the blanks cells by each respective cell address, and
put all of these in some kind of summary sheet. For instance, clicking the
commandbutton would result in a €œSummary€� sheet being created and all blank
cells would be listed by cells address, such as $D$5, $F$14, $J$92...if these
cells are blank.

Any ideas?

Kind Regards,
Ryan---

This is unreal! Exactly what I was thinking of!!! Tom, please tell me, did
you have this code saved away somewhere, or did you just create it after you
read my post?

Thanks so much.

Ryan---


"Tom Ogilvy" wrote:

Dim r as Range, cell as Range
Dim rw as Long
On Error resume Next
set r = cells.Specialcells(xlBlanks)
On error goto 0
if not r is nothing then
for each cell in r
rw = rw + 1
worksheets("Sheet2").Cells(rw,1).Value = cell.Address
Next
End if

this assumes you don't have more than 8192 discontiguous areas of blank
cells.

--
Regards,
Tom Ogilvy

"ryguy7272" wrote:

Hello Experts! I used the macro recorder to find and color all blank cells
in a used range in my worksheet. It is easy to do by firing up the recorder,
hitting Ctrl + g, ckicking €˜Special, selecting Blanks, and filling in the
blanks with a color. I am wondering if there is a way to do something
similar, but identify the blanks cells by each respective cell address, and
put all of these in some kind of summary sheet. For instance, clicking the
commandbutton would result in a €œSummary€� sheet being created and all blank
cells would be listed by cells address, such as $D$5, $F$14, $J$92...if these
cells are blank.

Any ideas?

Kind Regards,
Ryan---