1


I have a vba code that copies a file multiple times and renames the output after a list of names in column D from sheet "Linkuire".
Column D is full of concatenate formulas that bring data into cells till D1000.
When the concatenate formulas return "" (as in nothing) i want the code to ignore that cell. 

 ' the range of cells that contain the rename list
With ActiveWorkbook.Sheets("Linkuire")  
 Set rRenameList = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
End With

Now it just takes into account all D2 to D1000 cells even if some are = ""

How can I make the code ignore all cells where the return of the concatenate is "" ? (I have the same problem with a vba code that converts a certain sheet into pdf - with data got from concatenate formulas. It converts all cells even if concatenate return "" and is blank)

Thank you..

MisterA
  • 153
  • 5
  • 16

2 Answers2

1

edited since pure SpecialCells() approach wouldn't work

You could use following two approaches and avoid looping:

  • AutoFilter() and SpecialCells() approach:

    With ActiveWorkbook.Sheets("Linkuire")
    With .Range("D1", .Cells(.Rows.count, "D").End(xlUp))
        .AutoFilter Field:=1, Criteria1:="<>" '<--| filter out blanks
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then Set rRenameList = .Resize(.Rows.count - 1).Offset(1).SpecialCells(xlCellTypeVisible)
    End With
    .AutoFilterMode = False
End With

  • Formula and SpecialCells() approach

    With ActiveWorkbook.Sheets("Linkuire")
    With .Range("D2", .Cells(.Rows.count, "D").End(xlUp))
        .Offset(, 1).FormulaR1C1 = "=IF(RC[-1]="""", 1,"""")"
        Set rRenameList = .Offset(, 1).SpecialCells(xlCellTypeFormulas, xlNumbers).Offset(, -1)
        .Offset(, 1).ClearContents
    End With
End With

    in this approach you're writing a formula in a "helper" column I chose to be the adjacent to the right. It can be adjusted to any other offset

user3598756
  • 28,893
  • 4
  • 18
  • 28
  • Will that work for you? I tried something like that, and it still just grabs all cells with a formula...thus grabbing all cells, since OP has "`=Concat(A1,B1)`" in all the cells. – BruceWayne Dec 28 '16 at 22:22
  • User3598756s solution did not work. Still got all the cells no matter if the formula resulted in something or "". Bruce, your code worked tho just perfect :) I market it as an answer, thank you – MisterA Dec 28 '16 at 22:28
  • You both are right! I edited my answer with two different (working!) approaches – user3598756 Dec 29 '16 at 07:07
  • 1
    @MisterA, did you try these two (alternative) codes? They are quite short and fast – user3598756 Dec 29 '16 at 15:09
  • Thank you. Sorry for the late reply.After I solved this problem I jumped right to the next issue I had with my project but I saved your alternatives also for future ocasions :) – MisterA Jan 20 '17 at 22:33
0

This should work. It will loop through your range, and only add the cell address to your rRenameList when the length is greater than or equal to 1.

Sub Test()
' Adapted from http://stackoverflow.com/a/8320884/4650297

  Dim rng1 As Range, rRenameList As Range, cel As Range
  With ActiveWorkbook.Sheets("Linkuire")
    Set rng1 = .Range("D2", .Cells(.Rows.Count, "D").End(xlUp))
  End With

  For Each cel In rng1
    If Len(cel) >= 1 Then
        If Not rRenameList Is Nothing Then
            Set rRenameList = Union(rRenameList, cel)
        Else
        ' the first valid cell becomes rng2
            Set rRenameList = cel
        End If
    End If
  Next cel

Debug.Print rRenameList.Address
End Sub
BruceWayne
  • 22,923
  • 15
  • 65
  • 110