# Macro - Formula to loop through next Column excluding cell values of countblank = 1

Status
Not open for further replies.

#### ffionnah

##### Board Regular
Hia,

I have data from G3 through to BBI98 and there are many blanks within this range.

What I am trying to do is concat 2 numbers, starting at E3, while skipping cells with the value of CountBlank = 1 and looping to the next column. This concat formula would continue down through column E.

The formula is simply: =IF(COUNTBLANK(G3)=1,"",(CONCAT(\$G\$1,G3)))

Once this reaches G98, I need it to loop to H3 and start all over again.

=IF(COUNTBLANK(H3)=1,"",(CONCAT(\$H\$1,H3)))

Once this reaches H98, I need it to loop to I3 and start all over again.

=IF(COUNTBLANK(I3)=1,"",(CONCAT(\$I\$1,I3)))

Etc all the way to BBI98.

So, what I am hoping to do is create a macro that will loop this and skip any cells that CountBlank = 1 (since they all have formulas within them as well), while still looping through row 98. This is because there could be data in all rows but 20,21,22,etc but I dont want this formula to just stop at the blanks and skip the rest of the potential data through row 98.

Here is a very small example:

 109​ 102​ 570​ 571​ 234​ I​ I​ I​ I​ S​ BUS​ 1558​ 1558​ 1558​ 1558​ BUS​ 1255​ 1255​ 1255​ 1255​ CAN​ 1730​ 1730​ 1730​ 1730​ CAR​ 1205​ 1205​ 1205​ 1205​ HAM​ 1689​ 1689​ 1689​ 1689​ HAR​ 1083​ 1083​ 1083​ 1083​ HAR​ 1306​ 1306​ 1306​ 1306​ IRB​ 1275​ 1275​ 1275​ 1275​ KHA​ 1174​ 1174​ 1174​ 1174​ MAI​ 1306​ 1306​ 1306​ 1306​ MAY​ 1720​ 1720​ 1720​ 1720​ MER​ 1760​ 1760​ 1760​ 1760​ MOH​ 1336​ 1336​ 1336​ 1336​ MUR​ 1265​ 1265​ 1265​ 1265​ NOE​ 1972​ 1972​ 1972​ 1972​ PAN​ 1669​ 1669​ 1669​ 1669​ PAT​ 1164​ 1164​ 1164​ 1164​ POW​ 1811​ 1811​ 1811​ 1811​ RIV​ 1619​ 1619​ 1619​ 1619​ TOR​ 1568​ 1568​ 1568​ 1568​ LAC​ AND​ COO​ DEM​ GOD​ MAR​ MAY​ OFF​ SUC​ WIL​ COZ​ KLA​ PRI​ SIL​ THI​ VAS​ SEN​ SHA​ KAR​ VEG​ WHI​ BAR​ REZ​ ROG​ WAR​ DUN​ LAM​ HEN​ GAR​ HAL​ LEE​ JON​ KAP​ KAS​ KEN​ LEE​ LOC​ LON​ LOW​ BRA​ MIZ​ RAN​ ALT​ BUR​ GON​ 1295​ ROB​ PEL​ BOL​ 1295​ RIS​ 1851​ SCH​ 1609​ THO​ 1083​ WAL​ 1083​ WIL​ 1669​ WEC​ BAR​ RIC​ ART​ BHA​ BLI​ DOE​ GOR​ KRI​ LAN​ LAR​ MER​ MIL​ OUH​ PAR​ TAL​ BER​ ELL​ AMA​ CHI​ PAT​ SHA​ PUG​

<tbody>
</tbody>

Is this possible!? Also, is it possible to have the formula not calculate the CountBlank = 1 value cells so there are not blank cells within the concat Column E?

Thank you!

Last edited:

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
So far, all i have is this code which doesnt circulate through even the first column nor does it loop

Code:
``````Sub ConcTrial()

Range(ActiveCell.Offset(2, 4), ActiveCell.End(xlDown).Offset(2, 4)).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"

End Sub``````

I tried something like this but I cannot seem to get it to actually loop through the next column, while still pasting the formula into column E.

Code:
``````Sub ConcTrial()
Dim LastRowColumnE As Long
LastRowColumnE = Cells(Rows.Count, 7).End(xlUp).Row
For Y = 7 To Cells(3, Columns.Count).End(xlToRight).Column

x = 3

If Cells(x, Y) <> 1 Then
Range("E3:E" & LastRowColumnE).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"
End If
Do Until Cells(x, Y).Value = Blank
x = x + 1
Loop

Next Y

End Sub``````

I have changed some of the data around a little and didn't want to confuse anyone who may potentially try to help. Please disregard the above attempts, as my other post was locked.

I am trying to create a macro to paste a concatenation formula into a new column for the range of Columns G3 to BBI98. The current code I have is looping, but it is not continuously pasting this concatenation formula down through the new column.

Is there a way for the concatenation formula to paste the info for the first group G3 to G98 in E3 to E98 and then loop back to H3 to H98 and paste this concatenation formula in E99 to E198... etc?

Code:
``````Sub ConcTrial()
Dim LastRowColumnE As Long
Dim RL As Long, Cl As Integer
RL = Cells(3, 7)

LastRowColumnE = Cells(Rows.Count, 7).End(xlUp).Row
For i = 7 To 1407

x = 3
If Cells(x, i) <> 1 Then
Range("E3:E" & LastRowColumnE).FormulaR1C1 = "=IF(COUNTBLANK(RC[2])=1,"""",(CONCAT(R1C7,RC[2])))"
End If

Next i

End Sub``````

Closed at Op's request

Status
Not open for further replies.

Replies
3
Views
683
Replies
1
Views
438
Replies
1
Views
601
Replies
2
Views
466
Replies
3
Views
486

1,203,067
Messages
6,053,333
Members
444,654
Latest member
Rich Cohen

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back