How to ignore empty cells in macro

Question

looking for a little help here. I am trying to get my macro to paste the values of only cells with information inside of them to a list. Unfortunately, my macro is pulling all of the empty cells as well, but pasting them as empty cells. Does anyone have an idea of how to get my macro to completely ignore empty cells? Also, I am trying to have this macro paste to C38 but I think that I might have my references messed up..

Range("C11").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "3"
Range("C12").Select
SolverOk SetCell:="$A$20", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$26:$V$29", _
    Engine:=2, EngineDesc:="Simplex LP"
SolverOk SetCell:="$A$20", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$26:$V$29", _
    Engine:=2, EngineDesc:="Simplex LP"
SolverSolve UserFinish:=True
        iMaxRow = 17
For iCol = 3 To 21
For iRow = 1 To iMaxRow

With Worksheets("Summary").Cells(iRow, iCol)
    ' Check that cell is not empty.
    If .Value = "" Then
        'Nothing in this cell.
        'Do nothing.
    Else
        ' Copy the cell to the destination
        Worksheets("Summary").Cells(3, 38).Value = .Value
    End If
End With

Next iRow
Next iCol

Sheets("Summary").Select

score 1 · Accepted Answer · answered Aug 13 '13 at 15:52

1

Give this a try:

Sub tgr()

    Dim ws As Worksheet
    Dim rIndex As Long
    Dim cIndex As Long

    Set ws = Sheets("Summary")

    Range("C11").Value = "3"
    SolverOk SetCell:="$A$20", MaxMinVal:=1, ValueOf:=0, ByChange:="$B$26:$V$29", _
             Engine:=2, EngineDesc:="Simplex LP"
    SolverSolve UserFinish:=True

    For cIndex = Columns("C").Column To Columns("U").Column
        For rIndex = 1 To 17
            If Len(Trim(ws.Cells(rIndex, cIndex).Text)) > 0 Then
                ws.Cells(Rows.Count, "C").End(xlUp).Offset(1).Value = ws.Cells(rIndex, cIndex).Text
            End If
        Next rIndex
    Next cIndex

    ws.Select

    Set ws = Nothing

End Sub

answered Aug 13 '13 at 15:52

tigeravatar

26,199
5
30
38

This is very close, is there a way to reference columns 2, 6, 10, and 14? instead of 1 to 17 like you have listed above? – Jimmy Live Aug 13 '13 at 18:26
That pattern is every four columns starting at column 2 (B), so you could change the For cIndex line to the following: `For cIndex = Columns("B").Column To Columns("N").Column Step 4` The Step 4 will make it go to every 4th column – tigeravatar Aug 13 '13 at 18:53
Also, the 1 to 17 is the row numbers, not the column numbers. You can change rIndex as needed though – tigeravatar Aug 13 '13 at 18:55
Yes, Rows, sorry. Awesome this is exactly what I needed. One last question, I'm doing this at three different points throughout the macro. How do I specify where the output is on each of them? This one is outputting at C30 but I don't see where that is determined – Jimmy Live Aug 13 '13 at 19:03
The output is at `Cells(Rows.Count, "C").End(xlup).Offset(1)` which basically says "Go to the very bottom cell of column C and simulate pressing Ctrl+Up to go to the last populated cell in the column, then go down 1 cell and put the result there" – tigeravatar Aug 13 '13 at 19:07
Is there any way that I can make it output on a specific cell? Say... Cells("C20")? – Jimmy Live Aug 13 '13 at 19:09
You can, but then that cell will only show the last result of the loop. Using the method I provided, each result goes in its own cell in Column C on sheet 'Summary'. If you just say "C20", then it will get updated with the first result, then the second result (which will override the first result), then the third result (which will override the second result) and so on until the last result which will be the only one displayed. If you need all results in a single cell, see eyeinthebrick's answer for how to do so. – tigeravatar Aug 13 '13 at 19:27
That's ok as long as I have the function in my macro three separate times to output to 3 separate locations on my Summary sheet. I tried to set the cell as C20, but I'm getting a debug error. I guess I still don't fully understand where I have to put my reference to Cell C20 – Jimmy Live Aug 13 '13 at 19:32

score 0 · Answer 2 · edited Aug 13 '13 at 13:49

0

if you want to save everything in cell("C38") then you should use this line

Worksheets("Summary").Cells(3, 38).Value = Worksheets("Summary").Cells(3, 38).Value + .Value + chr(11)

edited Aug 13 '13 at 13:49

Kazimierz Jawor

18,861
7
35
55

answered Aug 13 '13 at 13:47

eyeinthebrick

528
1
5
21

I want to save any information into individual cells, so pretty much whenever there is a cell with information, I want it in C38, the next time there is information, I would like it in C39, and so on. Does that make sense? – Jimmy Live Aug 13 '13 at 13:54
then you may probably use a counter: Worksheets("Summary").Cells(3, 38+i).Value = .Value i= i + 1 – eyeinthebrick Aug 15 '13 at 13:02

How to ignore empty cells in macro

2 Answers2