VBA copying values only from multiple ranges ignoring empty/blank cells. Pls Help!

[iCountWater]

Very new to VBA. Like... VERY! Have simplified what I'm actually working on in the hope that if anyone can solve my problem as presented I can work with that and further my own understanding of VBA. Need to copy from 9 different ranges that may have all data, some data or no data (see: attached image "sheet1") to a sequential list on other worksheet (see: attached image "sheet 2") ignoring empty/blank cells. Data in "Type" column of ranges is from drop-down lists, remaining data ("V1- V4") is auto populated accordingly via formulas. Need to copy the values only and, as mentioned ignore empty/blank. Managed to get ranges to copy but not ignoring empty/blanks. And the code is ugly. Really ugly. I need help.

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub iCountWater()
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("A3:E7", "G3:K7", "M3:Q7", "A11:E15", "G11:K15", "M11:Q15", "A19:E23", "G19:K23", "M19:Q23")
   
   For i = 0 To UBound(Ary)
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(5, 5).Value = Sheets("Sheet1").Range(Ary(i)).Value
   Next i
End Sub

 

[iCountWater]

Hi & welcome to MrExcel.
How about

Sub iCountWater()
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array("A3:E7", "G3:K7", "M3:Q7", "A11:E15", "G11:K15", "M11:Q15", "A19:E23", "G19:K23", "M19:Q23")
  
   For i = 0 To UBound(Ary)
      Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(5, 5).Value = Sheets("Sheet1").Range(Ary(i)).Value
   Next i
End Sub

Thanks so much! Works perfectly

 

[Fluff]

You're welcome & thanks for the feedback.