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

Status
Not open for further replies.
F

ffionnah

Board Regular
Joined
Jun 12, 2018
Messages
61
Hia,
Thank you in advance for your help.

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:

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


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

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
So far, all i have is this code which doesnt circulate through even the first column nor does it loop :laugh::(

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
 
Upvote 0
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
 
Upvote 0
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. :biggrin:




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?

Thank you, in advance, for your help!:biggrin:

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
 
Upvote 0
Status
Not open for further replies.

Similar threads

J
  • Question Question
Excel macro for Remove comma's and spaces from a cell and place the data in different cell
Replies
3
Views
1K
JEC
JEC
S
  • Question Question
Code to use multiple trigger cells to hide / unhide rows of corresponding question sets
Replies
1
Views
1K
dmt32
D
C
  • Question Question
Need Help with VBA Code - Copy data below and above a specific text
Replies
1
Views
867
onlyadrafter
O
T
  • Locked
  • Question Question
Procedure loo lagre
Replies
2
Views
663
Smitty
Smitty
T
  • Question Question
Procedue too large
Replies
3
Views
666
p45cal
P

Forum statistics

Threads
1,223,910
Messages
6,175,320
Members
452,635
Latest member
laura12345

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top