CountA and cell references in VBA

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137
How can I change the cell references in this code to be flexible for my requirement? This code finds the first used cell in a small range. I want to examine each group of cells and then move on to the next column [cols I to N]

VBA Code:
With ActiveSheet
   
    If WorksheetFunction.CountA(Range("K2:K12")) = 0 Then
     
        MsgBox "empty space"
        Exit Sub
       
        Else
       
        MsgBox "Cell with value is row " & Range("K2:K12").Find(What:="*", After:=Range("K2")).Row
    End If
End With

I want to use StartRow, EndRow and c to identify the columns and each small range of cells in the column---the number of rows varies something like this

VBA Code:
With ActiveSheet
   
      If WorksheetFunction.CountA("Range(cells(startrow,c)):Range(cells(endrow,c))") = 0 Then   'if entire range is null then exit
          
      Exit Sub
   
    Else
   
    MsgBox "Cell with value is row " & ((Cells(StartRow, c): Cells(EndRow, c)).Find(What:="*", After:=Cells(StartRow, c)).Row
    End If
    End With

Sadly my effort doesn't work :( Can I use the cell references with the CountA?
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,973
Office Version
  1. 365
Platform
  1. Windows
Your range declaration is not correct.
Quite frankly, I find it easier to create the range, and then insert that into the formula, i.e.
VBA Code:
Dim rng as Range
Set rng = Range(Cells(StartRow, c), Cells(EndRow, c))
If WorksheetFunction.CountA(rng) = 0 Then
You could do it all in one step, but I find this way easier to understand, follow, and support.
 
Solution

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137
thanks Joe4 this solves the first part when there is no data but I'm getting a "Code execution has been interrupted" error message when there is data in the range after the lower Msgbox has displayed.

VBA Code:
Set rng = Range(Cells(StartRow, c), Cells(EndRow, c))
    If WorksheetFunction.CountA(rng) = 0 Then
    
    MsgBox "Column space is empty"
    Exit Sub
    
    Else
    
    MsgBox rng.Find(What:="*", After:=Cells(StartRow, c)).Row
    
   End If

Do you know what causes this?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,973
Office Version
  1. 365
Platform
  1. Windows
It works fine for me.
What are your values for c, StartRow, and EndRow?

If you cannot figure it out, please post your complete code, and an image of your data.
 

Westbury

Board Regular
Joined
Jun 7, 2009
Messages
137
I changed the second Msgbox to Z=... and it picked up the row number without error (this is what I'll actually use in the wider context). I've changed it back to Msgbox as a test and it operates without error today :unsure:.

I think we can call it a solution. Thanks for your help.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,973
Office Version
  1. 365
Platform
  1. Windows
You are welcome.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,839
Messages
5,638,652
Members
417,040
Latest member
EC1728

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
Top