Excel VBA range stops half way.

I have an Excel sheet with data like so:

Capture123.JPG
I'm creating a macro that will automatically fill any blank cells in column B with 0 with this code:

Sub testfill()

    With Me.Range("b2", Range("b" & Rows.Count).End(xlUp))
    
        .SpecialCells(xlCellTypeBlanks).Select
        Selection.Value = 0
        
    End With
    
End Sub

Open in new window


But doing so can fill in all but the last few rows that are empty.

Capture1233.JPG
I can understand why because the range only detects the last cell of the column with data as the end range. But how do I get it to follow through all the way to the end?
Hans J.HauAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
Try this, you need last row from A

Sub testfill()

    With Me.Range("b2", Range("a" & Rows.Count).End(xlUp))
    On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).Value = 0
        On Error GoTo 0
    End With
    
End Sub

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Saqib Husain, SyedEngineerCommented:
Change

With Me.Range("b2", Range("b" & Rows.Count).End(xlUp))

to

With Me.Range("b2", Range("a" & Rows.Count).End(xlUp))
0
Roy CoxGroup Finance ManagerCommented:
Please note that to find the row that you need you use the longest column, i.e. A.

You do not need to Select the Range

SpecialCells throw an error if the no cells match the criteria so use an error handler with them
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Saqib Husain, SyedEngineerCommented:
Actually, I sneezed before posting  ;-)
1
Roy CoxGroup Finance ManagerCommented:
Hay Fever?
0
LearnReporting Automation ExpertCommented:
Hi Hans,

Please try with this coding

Sub test()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = 0
End Sub

Open in new window

0
Fabrice LambertFabrice LambertCommented:
Hi,

Using Range.End(xlUp) is only reliable if you are 100% sure that your column can't hold any null value at the last row.

You might want to use Ron de Bruin's function to retrieve the last row (or column) holding data in an Excel worksheet, as an alternative and generic way:
https://www.rondebruin.nl/win/s9/win005.htm

PS: The selection object is just a major source of troubles, I do not advice using it.
0
Roy CoxGroup Finance ManagerCommented:
Really must sign up to learn Tamil, I never realised the importance of selecting everything.

By the way Hans the Me keyword will only work if the code is in the actual worksheets module .
1
Roy CoxGroup Finance ManagerCommented:
Final comment, Learn Excel in Tamil's code works on the wrong column!!!!
0
LearnReporting Automation ExpertCommented:
Is it Roy ?, it's working fine on Column B, i have checked now again,

I don't think this is wrong way of coding, since running without any error, anyway i am not too expert like Roy and Subodh Tiwari (Neeraj), i am just beginner

I will keep on learning from experts like you, thanks!
0
Roy CoxGroup Finance ManagerCommented:
So your code works on the whole range, it will convert blanks in Column A as well. Even so, it's rubbish coding.
1
Roy CoxGroup Finance ManagerCommented:
Column A does not have any blanks in the example, you should not assume this will always be the case.
0
LearnReporting Automation ExpertCommented:
Good question Roy, thanks for that
0
LearnReporting Automation ExpertCommented:
Hi Hans J.Hau

if you will not have any blanks in Column A please go with Option 1 coding,

Else go with Option 2 coding.

Hope this will help!

Option 1


Sub test()
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = 0
End Sub

Open in new window




Option 2

Sub test()

Dim Lastrow As String
Lastrow = Range("A" & Rows.Count).End(xlUp).Row
Range("B2:B" & Lastrow).Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Value = 0
End Sub

Open in new window


Thanks!

Learn Excel in Tamil :)
0
Roy CoxGroup Finance ManagerCommented:
Yet again - Deja Vu
0
Fabrice LambertFabrice LambertCommented:
Come on !

We already told ya that using the Selection object is a bad idea.
First: It is user dependant (a simple click change it).
Second: You have no guarantee that it hold the values neither it is the type you expect (selection can be a range, a sheet, a chart or whatever).
Third: It is slow as hell.
Fourth: It make changes on the graphic interface wich is frowned upon by most users (plus it is ugly).
0
LearnReporting Automation ExpertCommented:
😀
0
Hans J.HauAuthor Commented:
Thanks for the suggestions. While waiting for the question to be published, I found the answer by accident:
 With Me.Range("d:d")
    
        .SpecialCells(xlCellTypeBlanks).Select
        Selection.Value = 0
        
    End With

Open in new window

I always thought doing so will affect the entire column and fill in 0 until the absolute last row, but apparently it stops right where I want it to.
1
Roy CoxGroup Finance ManagerCommented:
Not the best solution. As I said earlier there is no need to select the range and also it is best to use an error handler with SpecialCells.

That code works on Column D, not B as requested earlier.
0
Hans J.HauAuthor Commented:
Oops... Forgot to change the range. I was testing out on another column at the time. I agree its not perfect but it'll do for now and I'll tinker with it once things settle down at work a little.
0
Hans J.HauAuthor Commented:
As you said, this version is much better than my previous one. Will try and remember this one.
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VBA

From novice to tech pro — start learning today.