Creating a new worksheet by checking for blank values in a range

[Geoff]

Hello, I am trying to create a new worksheet from an existing range of
data (pivot table copy/pasted values). This idea is to scan this range
and identify where there are no, or blank, values in a cell and return
take the heading (row and column) value for the blank cell and paste
them into a new worksheet for each occurrance. I have an example below
with A through C as column headings and 1 through 3 as row headings....

A B C
1 4 5 6
2 4 6
3 5

Using this example, I would want the new worksheet to populate as
such....

3 A
2 B
3 C

This is basically a tool to identify each area where there is missing
data and populate a new worksheet with those areas.

Can anyone help? This is beyond my programming capabilities, which are
quite modest.

Many Thanks!
Geoff

[Martin Krastev]

Try this code:

Dim sh As Worksheet
Dim r As Range, rng As Range
Dim i As Integer

i = 1
Set rng = Selection ' get the selected range
Set sh = ActiveWorkbook.Sheets.Add ' add a new sheet
For Each r In rng 'iterate through all cells of rng
If r.Value = "" Then 'check if cell is empty
sh.Cells(i, 1).Value = r.Address ' write the empty cell address
in the new sheet
i = i + 1 'goto next row
End If
Next r


"Geoff" wrote:

> Hello, I am trying to create a new worksheet from an existing range of
> data (pivot table copy/pasted values). This idea is to scan this range
> and identify where there are no, or blank, values in a cell and return
> take the heading (row and column) value for the blank cell and paste
> them into a new worksheet for each occurrance. I have an example below
> with A through C as column headings and 1 through 3 as row headings....
>
> A B C
> 1 4 5 6
> 2 4 6
> 3 5
>
> Using this example, I would want the new worksheet to populate as
> such....
>
> 3 A
> 2 B
> 3 C
>
> This is basically a tool to identify each area where there is missing
> data and populate a new worksheet with those areas.
>
> Can anyone help? This is beyond my programming capabilities, which are
> quite modest.
>
> Many Thanks!
> Geoff
>
>

[Geoff]

I believe you are on to something with that, but there is one
discrepancy.

The "headings" I was referring to are actual data in cells, not the
cell address. For instance, the columns are patient numbers (1-250)
and the rows are patient data (text such as Cycle1). Sorry for the
confusion. In an ideal world, the 1st column on the new spreadsheet
would contain the patient number corresponding to the blank cell and
the second column would contain the patient data corresponding to the
blank cell .

Thanks!!!
Geoff

[Martin Krastev]

Then try this:
Sub a()
Dim sh As Worksheet
Dim r As Range, rng As Range
Dim i As Integer

i = 1
Set rng = Selection ' get the selected range
Set sh = ActiveWorkbook.Sheets.Add ' add a new sheet
For Each r In rng 'iterate through all cells of rng
If r.Value = "" Then 'check if cell is empty
sh.Cells(i, 1).Value = rng(r.Row - 1, 0).Value ' write the cell
value at left of range in col1
sh.Cells(i, 2).Value = rng(0, r.Column - 1).Value ' write the
cell value at top of range to col2
i = i + 1 'goto next row
End If
Next r

End Sub

To get it working you select the data range(excluding header columns and
rows) and run the macro. Good Luck!

"Geoff" wrote:

> I believe you are on to something with that, but there is one
> discrepancy.
>
> The "headings" I was referring to are actual data in cells, not the
> cell address. For instance, the columns are patient numbers (1-250)
> and the rows are patient data (text such as Cycle1). Sorry for the
> confusion. In an ideal world, the 1st column on the new spreadsheet
> would contain the patient number corresponding to the blank cell and
> the second column would contain the patient data corresponding to the
> blank cell .
>
> Thanks!!!
> Geoff
>
>

[Geoff]

Excellent, all is working now! I did have to make a change to the
column and row indicators (r.Row - 3 and r.Column - 2) to get it to
work correctly. I have no idea why this is the case, I just messed
around with it until it worked. In any case, THANKS MARTIN!!! The new
code is below.........

Sub Reconcile()
Dim sh As Worksheet
Dim r As Range, rng As Range
Dim i As Integer

i = 1
Set rng = Selection
' get the selected range
Set sh = ActiveWorkbook.Sheets.Add
' add a new sheet
For Each r In rng
'iterate through all cells of rng
If r.Value = "" Then
'check if cell is empty
sh.Cells(i, 1).Value = rng(r.Row - 3, 0).Value
' write the cell value at left of range in col1
sh.Cells(i, 2).Value = rng(0, r.Column - 2).Value
' write the cell value at top of range to col2
i = i + 1
'goto next row
End If
Next r


End Sub