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

surkdidat

Active Member
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

Well-known Member
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

Active Member
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.

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

Well-known Member
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

Active Member
(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 101/06/2019 Adam1 ASteveStage 1 A03/06/2019Sophie2500 On Target
21235 Request 202/06/2019 Peter1 BPhilStage 1 B03/06/2019Ellie On Target
31236 Request 3 03/06/2019 Andy1 ATomStage 3 C05/06/2019 Kaitlyn6050
41237 Request 403/06/2019 Katie1 CStuartStage 4 B08/06/2019 Hannah

<tbody>
</tbody>







(LOOK UP TAB)
External REFERENCE
<strike></strike>
<strike></strike>
1235TypePrice
1236Comments
1237TypePriceComments

<tbody>
</tbody>

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

DanteAmor

Well-known Member
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

Active Member
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
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

Well-known Member
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

Active Member
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.



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

Well-known Member
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).
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top