• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 92
  • Last Modified:

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?
0
Hans J.Hau
Asked:
Hans J.Hau
  • 10
  • 5
  • 3
  • +2
1 Solution
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Saqib Husain, SyedEngineerCommented:
Actually, I sneezed before posting  ;-)
1
 
Roy CoxGroup Finance ManagerCommented:
Hay Fever?
0
 
LET (Learn Excel in Tamil)Reporting 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
 
LET (Learn Excel in Tamil)Reporting 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
 
LET (Learn Excel in Tamil)Reporting Automation ExpertCommented:
Good question Roy, thanks for that
0
 
LET (Learn Excel in Tamil)Reporting 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
 
LET (Learn Excel in Tamil)Reporting 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 10
  • 5
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now