problem working with CountBlank Function in VBA

megahurtz

New Member
Joined
Aug 16, 2011
Messages
17
Hi All,

My Macro opens an excel file and I've to count the number of blank spaces for every filled column. I'm using CountBlank function for that.
But, the code fails at the countBlank line by saying Type Mismatch.

I've attached a snippet of the code below. Please tell me where I'm going wrong.

Code:
Sub Snippet_Code()
 
Dim apdata as excel.application, wbdata as workbook
 
set apdata = new excel.application
Set wbData = apData.Workbooks.Open(Filename:="ABCD.xlsx")
 
For colnum = 1 to 50         'Columns from 1 to 50
 
    lrD = wbData.Sheets(1).Cells(wbData.Sheets(1).Rows.Count, colnum).End(xlUp).Row               'Find last row for every column
 
    BlnkCnt = WorksheetFunction.CountBlank(wbData.Sheets(1).Range(Cells(1, colnum).Address, Cells(lrD, colnum).Address))     'Find number of blank cells in the provided range
 
Next colnum
 
End Snippet_Code


My code fails at the BlnkCnt part. I also tried assigning the two cell addresses in two variables and passing the variables to the range method. But there again, I get the error of Method Range of class _Global Failed.

What might be the error?
Thanks a lot in Advance!!
 
Try amending the code to:

Code:
For colnum = 1 to 50         'Columns from 1 to 50
  With wbData.Sheets(1) 
    lrD = .Cells(.Rows.Count, colnum).End(xlUp).Row               'Find last row for every column
 
      BlnkCnt = WorksheetFunction.CountBlank(.Range(.Cells(1, colnum), .Cells(lrD, colnum)))     'Find number of blank cells in the provided range
   End With
Next colnum
 
Upvote 0
The code posted above was just a small snippet. I simply wanted to focus on the error area.
But even in my code, I implemented the WITH logic. Still it's giving me no luck.

This CountBlank function will work very nicely on the existing workbook. However, it's failing big time on external book. Please guide....

Thanks a lot in Advance! :)
 
Upvote 0
What about if you reference the Worksheetfunction with apdata:


BlnkCnt =
apdata.WorksheetFunction.CountBlank(.Range(.Cells(1, colnum), .Cells(lrD, colnum)))
 
Upvote 0

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