I need a simple code to resolve the following

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
18
Good morning

This should be easily done if not in Excel tehn in VBA

I need a simple code to resolve the following

I have a table with cells filled with numbers, or words, and cells which are empty, randomly.

I want to automatically depict next to each row the following example. row as 121, where the contents of that row would be

Example:

The contents of the row would be as below and the result would show, on a cell net to the row, 121

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / Filled cell = 121

Note: empty cells can be consecutively more than one and the result would still be 121, such as below

Filled cell / Empty cell /Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 121

Some other examples

Filled cell / Filled Cell / Filled cell / empty cell / empty cell / Filled cell = 31
Filled cell /Filled Cell / Filled cell
/ empty cell / empty cell / empty cell = 3
empty cell / empty cell / Filled cell /Filled Cell / Filled cell = 3


Hope someone can help.

Thank you for your attention

Regards
 

Some videos you may like

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,031
Office Version
2019
Platform
Windows
This will work
Book1
ABCDEFGH
2abbc121
3aaab31
4bbb3
Sheet10
Cell Formulas
RangeFormula
H2:H4H2=CONCAT(IFERROR(1/(1/FREQUENCY(IF($A2:$F2<>"",COLUMN($A2:$F2)),IF($A2:$F2="",COLUMN($A$2:$F$2)))),""))
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
 

Jogender singh

New Member
Joined
May 21, 2020
Messages
14
Office Version
2013
Platform
Windows
Sub cell_count()
Dim cell As Range, N As Long
'data in range ("a1:p4)
'column"Q"must be empty
'column "R" is used for final answer


Range("r:r").Clear
For Each cell In Range("a1:q1")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r1").Value = Range("r1").Value & N
N = 0
End If
Next cell

For Each cell In Range("a2:q2")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r2").Value = Range("r2").Value & N
N = 0
End If
Next cell

For Each cell In Range("a3:q3")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r3").Value = Range("r3").Value & N
N = 0
End If
Next cell

For Each cell In Range("a4:q4")
If IsEmpty(cell) = False Then
N = N + cell.Cells.Count
ElseIf N = 0 Then
Else
Range("r4").Value = Range("r4").Value & N
N = 0
End If
Next cell

End Sub
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
9,031
Office Version
2019
Platform
Windows
Make sure that you enter the formula and confirm the array only in F1, then fill it down. If you enter the formula into all of the cells then confirm the array as a whole then it will possibly give the incorrect results that you have seen.
 

rufuxinix

New Member
Joined
Jan 23, 2019
Messages
18
Make sure that you enter the formula and confirm the array only in F1, then fill it down. If you enter the formula into all of the cells then confirm the array as a whole then it will possibly give the incorrect results that you have seen.
Done!!
Thanks, Jason, your help is highly appreciated, I can now move on...
Bests regards.
1590337993152.png
 

Watch MrExcel Video

Forum statistics

Threads
1,095,727
Messages
5,446,162
Members
405,387
Latest member
michmichmich2020

This Week's Hot Topics

Top