Select only cells with data in a column

Technowolf

Board Regular
Joined
Aug 28, 2014
Messages
181
Hello! I'm looking for a better way to do this. Here's what I've got:

Code:
    Range("G2:G500").Select
     For Each xCell In Selection
       xCell.Value = CDec(xCell.Value)
        Next xCell

I have it just selecting everything right now since it can be anywhere from 50 rows to 250+ rows of data depending on the day and I run this a lot. It works fine but naturally it leaves me with a a ton of zeros after the last row of data entered to row 500. Rather than selecting G2:G500, how do I select just G2:Last cell with data?

Hopefully that made sense...
 
Try

Code:
Dim LR As Long
LR = Range("G" & Rows.Count).End(xlUp).Row
    
     For Each xCell In Range("G2:G" & LR)
       xCell.Value = CDec(xCell.Value)
        Next xCell
 
Upvote 0
Ah! Yes perfect, that was exactly what I needed. Was messing with the "G2:G" & thing earlier but couldn't get it right. Works great, thank you so much!
 
Upvote 0
Hello! I'm looking for a better way to do this. Here's what I've got:

Code:
    Range("G2:G500").Select
     For Each xCell In Selection
       [COLOR=#FF0000][B]xCell.Value = CDec(xCell.Value)[/B][/COLOR]
        Next xCell
What are you trying to do here that you are applying the CDec function to the value in the cells (I find that to be an odd function for you to be using in what I think you may be doing)?
 
Upvote 0
VoG's solution already worked fine for me. I was actually just fixing a minor pet peeve in one of my worksheets. I had a list of zip codes that I shorted from 10 to seven digits then copied to another worksheet through my macro, and when I did it created the little error tag on them, so I used that code to correct the error. It wasn't a big deal, like I said it was just a pet peeve. I'm sure there's a better overall way to do it, but it's already done and it all works, so I'm not going to bother changing it now.
 
Upvote 0
I was actually just fixing a minor pet peeve in one of my worksheets. I had a list of zip codes that I shorted from 10 to seven digits then copied to another worksheet through my macro, and when I did it created the little error tag on them, so I used that code to correct the error.
Instead of using this loop...
Code:
Range("G2:G500").Select
      For Each xCell In Selection
        xCell.Value = CDec(xCell.Value)
         Next xCell
you could have used this single line of code (it would be faster)...
Code:
Range("G2:G500").TextToColumns
 
Upvote 0
Again I reiterate what I have is already working for my proposes and I don't intend to change it, but thank you for your suggestion.
 
Upvote 0
Again I reiterate what I have is already working for my proposes and I don't intend to change it, but thank you for your suggestion.
My comment was meant for future readers of this thread with a similar need, and who may want to use more efficient code in their program, as much as my comment was meant for you.
 
Last edited:
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