I have the following code which works fine. Is there a way of reducing the number of lines when using multiple if statements. None are dependent on any others, and they all refer to the same worksheet and range. It just doesn't look right when scrolling through the code. Thought there might be a more concise way? I am not using these actual variable names.

    If Not c.Offset(0, 3).Value = "" Then
        var1 = True
        var2 = c.Offset(0, 3).Value
    End If
    
    If Not c.Offset(0, 4).Value = "" Then
        var3 = True
        var4 = c.Offset(0, 4).Value
    End If
    
    If Not c.Offset(0, 5).Value = "" Then
        var5 = True
        var6 = c.Offset(0, 5).Value
    End If
    
    If Not c.Offset(0, 6).Value = "" Then
        var7 = True
        var8 = c.Offset(0, 6).Value
    End If
    
    If Not c.Offset(0, 7).Value = "" Then
        var9 = True
        var10 = c.Offset(0, 7).Value
    End If

CodePudding user response：

You can use a switch statement, or instead of using a new if statement for each condition, create an else if which making the code shorter and more readable.

CodePudding user response：

Avoid Using a Ton of Variables (Array)

• When you have the need to use a ton of variables, especially of the same data type, it's time to consider using a data structure (array, dictionary, collection...).
• The following example explores the use of arrays. Since there is not enough information, using one of the other data structures may be more appropriate.
• To quickly learn about arrays, you could watch the videos from this

  The Code

  Option Explicit
  
  Sub ArraysInsteadOfTonsOfVariables()
  
      ' Using the Array function, write the column offsets to a variant array.
      ' (Using 'VBA.' in front of 'Array' ensures a zero-based array.)
      Dim ColumnOffsets() As Variant: ColumnOffsets = VBA.Array(3, 4, 5, 6, 7)
      
      ' Reference the initial cell ('iCell').
      ' ('ThisWorkbook' means the workbook containing this code.)
      Dim iCell As Range
      Set iCell = ThisWorkbook.Worksheets("Sheet1").Range("A1")
      
      ' Write the upper limit of the 'ColumnOffsets' array to a variable.
      ' (the lower limit is '0').
      Dim UB As Long: UB = UBound(ColumnOffsets)
      
      ' Define an array of the same size as the 'ColumnOffsets' array
      ' which will hold the booleans ('var1, var3, var5, var7, var9').
      Dim Booleans() As Boolean: ReDim Booleans(0 To UB)
      
      ' Define an array of the same size as the 'ColumnOffsets' array
      ' which will e.g. hold the strings ('var2, var4, var6, var8, var10').
      Dim Strings() As String: ReDim Strings(0 To UB)
      
      ' Print the title and the headers to the Immediate window ('Ctrl G').
      Debug.Print "For cell '" & iCell.Address(0, 0) & "'"
      Debug.Print "Index", "Offset", "Address", "Boolean", "String"
      
      Dim cCell As Range ' current (offsetted) cell
      Dim cIndex As Long ' current index
      Dim cString As String ' current string
      
      ' Loop through the elements of the arrays (they are all of the same size).
      For cIndex = 0 To UB
          ' Reference the current cell.
          Set cCell = iCell.Offset(, ColumnOffsets(cIndex))
          ' Write the current string (string representation
          ' of the current cell's value) to a variable.
          cString = CStr(cCell.Value)
          If Len(cString) > 0 Then ' is not ""
          ' or 'If cString <> ""' or 'If cString <> vbNullString'
              Booleans(cIndex) = True
              Strings(cIndex) = cString
          'Else ' is "" ' do nothing
              ' Since the 'Booleans' array is declared 'As Boolean',
              ' each of its elements is assigned the default value 'False'.
              ' Since the 'Strings' array is declared 'As String',
              ' each of its elements is assigned the default value '""'.
          End If
          ' Print what was written to the arrays
          ' to the Immediate window ('Ctrl G').
          Debug.Print cIndex, ColumnOffsets(cIndex), cCell.Address(0, 0), _
              Booleans(cIndex), Strings(cIndex)
      Next cIndex
      
  End Sub
  

  The Results

  For cell 'A1'
  Index         Offset        Address       Boolean       String
   0             3            D1            True          Peter
   1             4            E1            False         
   2             5            F1            True          Helen
   3             6            G1            False         
   4             7            H1            True          John