How to count number of used cells in the row before empty cell using VBA

M

motilulla

Well-known Member
Joined
Feb 13, 2008
Messages
2,371
Office Version
  1. 2010
Hello,

I need I VBA, which can count cells with numbers in the column starting from column "F" till first empty cell find in the row (ignoring last used columns) and put result in the column "D"

For example Row6; starting from F6 to M6 (before empty cell) there are 8-used cell
For example Row7; starting from F6 to N7 (before empty cell) there are 6- used cell
For example Row8; starting from F6 to P8 (before empty cell) there are 4- used cell
For example Row9; starting from F6 to K9 (before empty cell) there are 5- used cell

*ABCDEFGHIJKLMNOPQRSTU
1
2
3
4
5
68AB123712202122122
76AC123553647
84AD4590612
95AE1223456
10
11

For more detail the image is attached here.

Thank you all.

I am using Excel 2000

Regards,
Moti
 

Attachments

  • Count Used Cells.png
    Count Used Cells.png
    8.4 KB · Views: 12

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Do you need VBA code? If not, I have created a formula that will do this for you. Place the formula below in Cell D6 and copy it down.

Excel Formula: 
=COUNTA(INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":"&CELL("address",INDEX(INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":S"&ROW()),MATCH(TRUE,INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":S"&ROW())="",0)-1))))
 
Upvote 0
Crystalyzer said:
Do you need VBA code? If not, I have created a formula that will do this for you. Place the formula below in Cell D6 and copy it down.

Excel Formula: 
=COUNTA(INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":"&CELL("address",INDEX(INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":S"&ROW()),MATCH(TRUE,INDIRECT(CELL("address",INDEX(F6:S6,MATCH(TRUE,F6:S6<>"",0)))&":S"&ROW())="",0)-1))))
Click to expand...
Crystalyzer, you have written so long formula I really appreciate your effort, but unfortunately my excel 2000 version does not permit so long formula it the formula bar it has a character limitations. So far my request I need a VBA solution. I am grateful to you

Kind Regards,
Moti
 
Upvote 0
Here is a user-defined function to do what you want:
VBA Code: 
Function MyCount(myRow As Long) As Long

    Dim st As Boolean
    Dim ed As Boolean
    Dim num As Long
    Dim col As Long
    
    Application.Volatile
    
'   Set default values of start end end
    st = False
    ed = False
    
'   Set initial value of column to 6 (column "F")
    col = 6
    
'   Loop through each column in row starting at F
    Do Until (st = True) And (ed = True)
'       Check to see if there is a value in the current cell
        If Cells(myRow, col) <> "" Then
            st = True
'           Increment counter
            num = num + 1
        End If
'       Check to see if at end
        If (st = True) And Cells(myRow, col) = "" Then
            ed = True
        End If
'       Increment column
        col = col + 1
'       Make sure to bail out if at end of columns (fail-safe)
        If col > Columns.Count Then Exit Do
    Loop
    
    MyCount = num
    
End Function
So, then you could use it a formula for row 6 like this:
=MyCount(6)

Or, if you place the formula anywhere in row 6, you can just use:
=MyCount(Row())
which would allow you to copy it down for all rows.
 
Upvote 0
Solution
here you go

VBA Code: 
Sub UsedCells()
    Dim RowFirstUsedCell As Integer
    Dim RowLastUsedCell As Integer
    Dim r As Range
    Dim rFirstRow As Integer
    Dim rFirstCol As Integer
    Dim c As Integer
    
    'To use this macro you must first select the range you want to
    'determine the number of continuously used cells for.
    
    Set r = Selection
    rFirstRow = ActiveCell.Row
    rFirstCol = ActiveCell.Column
    c = 0
    
    'Loop through the rows of the selected range
    For i = 0 To r.Rows.Count - 1
        'Loop through the columns of each row of the selected range
        For j = 0 To r.Columns.Count - 1
            'Find the first column that contains a value
            If Cells(i + rFirstRow, j + rFirstCol).Value <> "" Then
                'Start to count used cells
                c = c + 1
            'check if a value has been found alread and break the loop if the current cell is blank
            ElseIf c > 0 Then
                Exit For
            End If
        Next j
        'Write the value in column 3 columns to the left of the current row being evaluated
        Cells(i + rFirstRow, rFirstCol - 3).Value = c
        'Reset the count
        c = 0
       Next i
End Sub
 
Upvote 0
Crystalyzer said:
here you go

VBA Code: 
Sub UsedCells()
    Dim RowFirstUsedCell As Integer
    Dim RowLastUsedCell As Integer
    Dim r As Range
    Dim rFirstRow As Integer
    Dim rFirstCol As Integer
    Dim c As Integer
   
    'To use this macro you must first select the range you want to
    'determine the number of continuously used cells for.
   
    Set r = Selection
    rFirstRow = ActiveCell.Row
    rFirstCol = ActiveCell.Column
    c = 0
   
    'Loop through the rows of the selected range
    For i = 0 To r.Rows.Count - 1
        'Loop through the columns of each row of the selected range
        For j = 0 To r.Columns.Count - 1
            'Find the first column that contains a value
            If Cells(i + rFirstRow, j + rFirstCol).Value <> "" Then
                'Start to count used cells
                c = c + 1
            'check if a value has been found alread and break the loop if the current cell is blank
            ElseIf c > 0 Then
                Exit For
            End If
        Next j
        'Write the value in column 3 columns to the left of the current row being evaluated
        Cells(i + rFirstRow, rFirstCol - 3).Value = c
        'Reset the count
        c = 0
       Next i
End Sub
Click to expand...
Crystalyzer, thank you for you're hard work, building a huge formula and now giving a VBA solution all worked ok, but I tell you the truth I will go with Joe4 solution because of the easier to use of it.

I appreciate your help and fulfilling my request.

Good Luck

Kind Regards,
Moti :)
 
Upvote 0
Hi, to count how many numbers in the first region from a starting cell it should be achieved with a no loop user function :​
VBA Code: 
Function CountFR(Rg As Range) As Long
         If IsEmpty(Rg) Then Set Rg = Rg.End(xlToRight)
         CountFR = Application.Count(IIf(IsEmpty(Rg(1, 2)), Rg, Range(Rg, Rg.End(xlToRight))))
End Function
Formula in cell D6 : =CountFR(F6) then copy down …​
 
Upvote 0
Joe4 said:
Here is a user-defined function to do what you want:
VBA Code: 
Function MyCount(myRow As Long) As Long

    Dim st As Boolean
    Dim ed As Boolean
    Dim num As Long
    Dim col As Long
   
    Application.Volatile
   
'   Set default values of start end end
    st = False
    ed = False
   
'   Set initial value of column to 6 (column "F")
    col = 6
   
'   Loop through each column in row starting at F
    Do Until (st = True) And (ed = True)
'       Check to see if there is a value in the current cell
        If Cells(myRow, col) <> "" Then
            st = True
'           Increment counter
            num = num + 1
        End If
'       Check to see if at end
        If (st = True) And Cells(myRow, col) = "" Then
            ed = True
        End If
'       Increment column
        col = col + 1
'       Make sure to bail out if at end of columns (fail-safe)
        If col > Columns.Count Then Exit Do
    Loop
   
    MyCount = num
   
End Function
So, then you could use it a formula for row 6 like this:
=MyCount(6)

Or, if you place the formula anywhere in row 6, you can just use:
=MyCount(Row())
which would allow you to copy it down for all rows.
Click to expand...
Joe4, All worked more than expectation, I like the function it is very practical to use it, the best part of it is if numbers are added or removed I am getting rapid results.

Thank you for building a great and very useful tool. I am very happy

Good Luck

Kind Regards,
Moti :)
 
Upvote 0
Marc L said:
Hi, to count how many numbers in the first region from a starting cell it should be achieved with a no loop user function :​
VBA Code: 
Function CountFR(Rg As Range) As Long
         If IsEmpty(Rg) Then Set Rg = Rg.End(xlToRight)
         CountFR = Application.Count(IIf(IsEmpty(Rg(1, 2)), Rg, Range(Rg, Rg.End(xlToRight))))
End Function
Formula in cell D6 : =CountFR(F6) then copy down …​
Click to expand...
Marc L said:
​
Do you see at least post #7 ? …​
Click to expand...
Marc L, just saw and tested, it is 2 line code look nice but the Joe4, function is more complete any way I appreciate your input.

Good Luck

Kind Regards,
Moti
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Counting how much number is in ascending order in a row.
2
Replies
12
Views
420
Peter_SSs
Peter_SSs
M
  • Solved
VBA to copy selected cell value to next empty cell in a range
Replies
3
Views
113
MeaclH
M
F
  • Question
using Variable in control names on user form
Replies
7
Views
104
RoryA
RoryA
B
  • Solved
Use Sheet1:D5 to find matching cell in Sheet2 Column A:A, and select cell (or row)
Replies
1
Views
84
brianv
B
W
  • Solved
Count every 15th non-empty cell in row
Replies
6
Views
233
Whitestar127
W

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

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