How to iterate over data with empty strings using VBA?
I am trying to iterate over non-contiguous data - basically looping through all the cells that matter in Range("K:M")
, taking into account that there might be some blank rows between the data and I will need to skip and go further, merge 3 cells into 1 in Range("H4")
.
Tried different approaches but every time it hits an empty string it stops. Below will be more like an endless loop that does what I need but can't work like breaking through at some point when there is no more data.
Sub GenerateStyleFabricColourV2()
Dim srcData As Range
Dim RowNum As Long
RowNum = 4
Set srcData = Range("K:M")
If Not IsEmpty(srcData.Value) Then
Do While IsEmpty(RowNum) = False
Cells(RowNum, 8).Value = Cells(RowNum, 11).Value & Cells(RowNum, 12).Value & Cells(RowNum, 13).Value
RowNum = RowNum + 1
Loop
End If
Range("H4").Select
Range(Selection, Selection.End(xlDown)).Select
End Sub
source to share
IsEmpty
refers to a cell that may or may not have a value (that is, empty or non-empty). It is not meant to do anything with an integer.
Sub GenerateStyleFabricColourV2()
Dim srcData As Range
Dim rowNum As Long, lastRow As Long
With Worksheets("sheet2")
lastRow = Application.Max(4, _
.Cells(.Rows.Count, "K").End(xlUp).Row, _
.Cells(.Rows.Count, "L").End(xlUp).Row, _
.Cells(.Rows.Count, "M").End(xlUp).Row)
With .Cells(4, "H").Resize(lastRow - 4 + 1, 1)
.FormulaR1C1 = "=rc[3]&rc[4]&rc[5]"
.Value = .Value2
End With
End With
End Sub
source to share
Sub GenerateStyleFabricColourV2()
Dim srcData As Range, rcell As Range
Set srcData = ThisWorkbook.Sheets("Sheet1").Range("K4:K" & ThisWorkbook.Sheets("Sheet1").Cells(Sheet1.Rows.Count, "K").End(xlUp).Row)
For Each rcell In srcData.Cells
rcell.Offset(0, -3).Value = rcell.Value & rcell.Offset(0, 1).Value & rcell.Offset(0, 2).Value
Next rcell
End Sub
It's pretty straight forward. I like to scroll one column and use an offset. However, I could simplify it.
source to share