Create Report(?) to report on rows with empty cells

surkdidat · Jun 14, 2019

Is there anyway, if there is a value in a row in Column B, on a separate sheet to create a report (or whatever) that will display Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN

Many thanks

DanteAmor · Jun 15, 2019

In other words, if in a row, for example row 8, from column R to column AN there is data, then hide row 8?

And what do you want, to pass all the visible rows to another sheet?

surkdidat · Jun 18, 2019

Hi

Sorry, will try and explain again

In a separate tab, I need it to look up (run Macro etc) if there is any value in column B (which will be the reference number)
In this tab, if, for example row 8, column B was 1234, it would look on that row and populate with the column header of any blanks. So, id R T V W AB were blank, whatever the column headers of these would appear alongside the reference number.

If row 9, column B was 1235 but all the columns listed above were populated, then it should ignore this completely.

If column B is blank, then no action should happen
On the main data tab, nothing needs to happen.

DanteAmor said:
In other words, if in a row, for example row 8, from column R to column AN there is data, then hide row 8?

And what do you want, to pass all the visible rows to another sheet?

DanteAmor · Jun 18, 2019

surkdidat said:
Hi

Sorry, will try and explain again

In a separate tab, I need it to look up (run Macro etc) if there is any value in column B (which will be the reference number)
In this tab, if, for example row 8, column B was 1234, it would look on that row and populate with the column header of any blanks. So, id R T V W AB were blank, whatever the column headers of these would appear alongside the reference number.

If row 9, column B was 1235 but all the columns listed above were populated, then it should ignore this completely.

If column B is blank, then no action should happen
On the main data tab, nothing needs to happen.

Now I understand less.
You can explain it with images with real data, actual sheet names, what you want to do with the information.

surkdidat · Jun 18, 2019

(DATA TAB)

Internal Ref

External Ref

DETAILS

DATE REC'D

REQUESTOR

AREA

PRIORITY

DEVELOPER

STATUS

STATUS2

STATUS DATE

ANALYST

Type

Price

Comments

1

1234

Request 1

01/06/2019

Adam

1

A

Steve

Stage 1

A

03/06/2019

Sophie

2

500

On Target

2

1235

Request 2

02/06/2019

Peter

1

B

Phil

Stage 1

B

03/06/2019

Ellie

On Target

3

1236

Request 3

03/06/2019

Andy

1

A

Tom

Stage 3

C

05/06/2019

Kaitlyn

60

50

4

1237

Request 4

03/06/2019

Katie

1

C

Stuart

Stage 4

B

08/06/2019

Hannah

(LOOK UP TAB)
External REFERENCE <strike></strike>	<strike></strike>
1235	Type	Price
1236	Comments
1237	Type	Price	Comments

DanteAmor said:
Now I understand less.
You can explain it with images with real data, actual sheet names, what you want to do with the information.

DanteAmor · Jun 18, 2019

surkdidat said:
Is there anyway, if there is a value in a row in Column B, on a separate sheet to create a report (or whatever) that will display Columns A through to L, and then any blank cells from Column R, S, T, U, V, W, X, Y, X, AA, AB, AE, AF, AK. AL. AM. AN

Many thanks

Try this macro.
Change data in red for your information.

Code:

Sub Create_Report()
    Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range
    Dim k As Long
    
    Application.ScreenUpdating = False
    Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]")
    Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP"[/COLOR])
    '
    sh2.Range("B2", Cells(Rows.Count, Columns.Count)).ClearContents
    For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp))
        k = Columns("N").Column
        Set f = sh1.Range("B:B").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            sh2.Cells(c.Row, "B").Resize(1, 12).Value = sh1.Cells(f.Row, "A").Resize(1, 12).Value
            For j = Columns("R").Column To Columns("AN").Column
                If sh1.Cells(f.Row, j).Value = "" Then
                    sh2.Cells(c.Row, k).Value = sh1.Cells(1, j).Value
                    k = k + 1
                End If
            Next
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

The macro reads the reference of the "lookup" sheet and looks for it in the "data" sheet, if find, it copies the columns of the A-L of the "Data" sheet and pastes them in the "look up" sheet.
Then check each column of the "data" sheet and if any cell is empty, then copy the title of that column from the "data" sheet and paste it on the "look up" sheet

surkdidat · Jun 19, 2019

Hi There

Thank you for this, although I am still confused.

All my data is in "DATA" tab.
The tab "LOOKUP" is where I want my results and is currently blank.

I have run the Macro and it just brings up a msgbox "END"

From above, I think what you are saying is missing what I want.

On "LOOKUP" it only should bring up a reference if there is a blank cell in that row. It then brings up the reference (from column B) and the row header for the blank cell.

Thank you

DanteAmor said:
Try this macro.
Change data in red for your information.

Code:

Sub Create_Report() Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, f As Range Dim k As Long Application.ScreenUpdating = False Set sh1 = Sheets("[COLOR=#ff0000]Data[/COLOR]") Set sh2 = Sheets("[COLOR=#ff0000]LOOK UP"[/COLOR]) ' sh2.Range("B2", Cells(Rows.Count, Columns.Count)).ClearContents For Each c In sh2.Range("A2", sh2.Range("A" & Rows.Count).End(xlUp)) k = Columns("N").Column Set f = sh1.Range("B:B").Find(c.Value, LookIn:=xlValues, lookat:=xlWhole) If Not f Is Nothing Then sh2.Cells(c.Row, "B").Resize(1, 12).Value = sh1.Cells(f.Row, "A").Resize(1, 12).Value For j = Columns("R").Column To Columns("AN").Column If sh1.Cells(f.Row, j).Value = "" Then sh2.Cells(c.Row, k).Value = sh1.Cells(1, j).Value k = k + 1 End If Next End If Next Application.ScreenUpdating = True MsgBox "End" End Sub

The macro reads the reference of the "lookup" sheet and looks for it in the "data" sheet, if find, it copies the columns of the A-L of the "Data" sheet and pastes them in the "look up" sheet.
Then check each column of the "data" sheet and if any cell is empty, then copy the title of that column from the "data" sheet and paste it on the "look up" sheet

DanteAmor · Jun 19, 2019

surkdidat said:
Hi There

Thank you for this, although I am still confused.

All my data is in "DATA" tab.
The tab "LOOKUP" is where I want my results and is currently blank.

I have run the Macro and it just brings up a msgbox "END"

From above, I think what you are saying is missing what I want.

On "LOOKUP" it only should bring up a reference if there is a blank cell in that row. It then brings up the reference (from column B) and the row header for the blank cell.

Thank you

You must have this on your "Lookup" sheet

<b>LOOK UP</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >External REFERENCE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1235</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1236</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1237</td></tr></table>

surkdidat · Jun 19, 2019

Ah. okay thank you. I have done that and it is populating data (running when in lookup tab) - however, it just seems to be a carbon copy of my data sheet, and copying text from cells that have text (and its the field text). Any empty fields on data sheet are just blanks on the lookup tab.

DanteAmor said:
You must have this on your "Lookup" sheet

<b>LOOK UP</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:124.51px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >External REFERENCE</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="text-align:right; ">1235</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">1236</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">1237</td></tr></table>

DanteAmor · Jun 19, 2019

surkdidat said:
Ah. okay thank you. I have done that and it is populating data (running when in lookup tab) - however, it just seems to be a carbon copy of my data sheet, and copying text from cells that have text (and its the field text). Any empty fields on data sheet are just blanks on the lookup tab.

This down here, was what you asked for:

on a separate sheet to create a report (or whatever) that will display Columns A through to L

Do you need something different?
Then you could explain again what you have, where you have it, and what you expect from the result.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

Create Report(?) to report on rows with empty cells

surkdidat

Active Member

DanteAmor

Well-known Member

surkdidat

Active Member

DanteAmor

Well-known Member

surkdidat

Active Member

DanteAmor

Well-known Member

surkdidat

Active Member

DanteAmor

Well-known Member

surkdidat

Active Member

DanteAmor

Well-known Member

Forum statistics

Share this page

Some videos you may like

This Week's Hot Topics