Select only cells with data in a column

[Technowolf]

Hello! I'm looking for a better way to do this. Here's what I've got:

    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...

 

[VoG]

Try

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

 

[Technowolf]

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!

 

[Rick Rothstein]

Hello! I'm looking for a better way to do this. Here's what I've got:

    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)?

 

[Technowolf]

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.

 

[Rick Rothstein]

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...

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)...

Range("G2:G500").TextToColumns

 

[Technowolf]

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.

 

[Rick Rothstein]

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.