CODEWITHSUNDEEP
excelvba
Eren Jaeger
Eren Jaeger

Reputation: 29

Find and output empty cells

The table contains column G = City, H = Department and J = Date. In the columns J Date some values are missing. I want to output these rows on a new worksheet with (column A) the rownumber, (column B) the city and (column) the departement.

enter image description here

The code I have looks like this but in the output all rows with a value in J = Date and the output is in the columns "G, H, J". I tried to change the columns in the code but I failed.

enter image description here

Sub missing()
Dim ws, wsOut As Worksheet
Set ws = ActiveWorkbook.Sheets("Table1")
Set wsOut = ActiveWorkbook.Sheets("output")

lastRow = ws.Range("G" & Rows.Count).End(xlUp).Row
lastRowOut = wsOut.Range("G" & Rows.Count).End(xlUp).Row + 1

For i = 1 To lastRow
    If (ws.Cells(i, 10).Value = "") _
    And _
    ((ws.Cells(i, 7).Value = "Peking") Or _
    (ws.Cells(i, 7).Value = "Tokio") Or _
    (ws.Cells(i, 7).Value = "London") Or _
    (ws.Cells(i, 7).Value = "Rom") Or _
    (ws.Cells(i, 7).Value = "Lissabon") Or _
    (ws.Cells(i, 7).Value = "Panama") Or _
    (ws.Cells(i, 7).Value = "Budapest") Or _
    (ws.Cells(i, 7).Value = "Prag") Or _
    (ws.Cells(i, 7).Value = "Dublin") Or _
    (ws.Cells(i, 7).Value = "Luxemburg")) _
    And _
    ((ws.Cells(i, 8).Value = "A") Or _
    (ws.Cells(i, 8).Value = "B") Or _
    (ws.Cells(i, 8).Value = "C") Or _
    (ws.Cells(i, 8).Value = "D") Or _
    (ws.Cells(i, 8).Value = "E") Or _
    (ws.Cells(i, 8).Value = "F") Or _
    (ws.Cells(i, 8).Value = "G") Or _
    (ws.Cells(i, 8).Value = "H") Or _
    (ws.Cells(i, 8).Value = "I") Or _
    (ws.Cells(i, 8).Value = "J")) _
    Then
        wsOut.Range("B" & lastRowOut & ":C" & lastRowOut).Value = ws.Range("G" & i & ":H" & i).Value
        wsOut.Range("A" & lastRowOut).Value = i
        lastRowOut = lastRowOut + 1
    End If
Next i

End Sub

Upvotes: 0

Views: 79

Answers (3)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Not sure i'm 100% with you, but

Dim r as range
dim c as range
dim a() as variant
dim i as long

set r=range("c2:c22").specialcells(xlcelltypeblanks)
redim a(1 to r.cells.count,1)
i=1

for each c in r.cells
    a(i,0) = cells(c.row,1)
    a(i,1)=cells(c.row,2)
    i=i+1
next c

' Output, to j1 on the same sheet.

cells(1,10).resize(ubound(a),2).value=a

Upvotes: 0

InjuredCoding
InjuredCoding

Reputation: 430

while i was writing this others have answered and honestly I like there solution but can also be done like this:

Sub missing()
    Dim ws, wsOut As Worksheet
    Set ws = ActiveWorkbook.Sheets("table")
    Set wsOut = ActiveWorkbook.Sheets("output")
    
    lastRow = ws.Range("A" & Rows.Count).End(xlUp).Row
    lastRowOut = wsOut.Range("A" & Rows.Count).End(xlUp).Row + 1
    
    For i = 2 To lastRow
        If ws.Cells(i, 3).Value = "" Then
            wsOut.Range("A" & lastRowOut & ":B" & lastRowOut).Value = ws.Range("A" & i & ":B" & i).Value
            wsOut.Range("C" & lastRowOut).Value = i
            lastRowOut = lastRowOut + 1
        End If
    Next i
End Sub

assuming table is in worksheet "table" and output is wanted in a worksheet called "output" [note output has to have a value somewhere in column A before the code is run or an error will be thrown]

Also the code you show does not appear to be trying to answer the question you've asked, it may just be that you took a wrong turn but it is quite different, let us know if we've all missed the point!

Upvotes: 1

Dominique
Dominique

Reputation: 17493

Click on cell "A1", press Ctrl+G and choose "Special", "current region" (that should select the whole array). Again press Ctrl+G and choose "Special", this type choose "Blanks".
In the address bar, type "No Date".
Press Ctrl+ENTER (don't forget the control-button).

You can record this into a macro.

Have fun :-)

Oh, by the way, this is wrong:

If Cells(i, 1).Value = "Peking" Or "Tokio" Or "London" Or ...

It should be something like:

If Cells(i, 1).Value = "Peking" Or_
   Cells(i, 1).Value = "Tokio" Or_
   ...

(The underscore after "Or" is just to explain VBA that this should be treated as one single line.)

Upvotes: 0

Related Questions