VBA Excel - select all rows with value in a column

Question

I have a spreadsheet named "Copy" sorted by numeric values (lowest to highest) in column H. The sheet has headers in row 1. The lowest value in column H is 0. I would like to find the last row that has column H equal 0 and then select the range of rows where column H equals 0 along with the header row.

Thanks!

Jeanno · Accepted Answer · 2015-03-03 19:56:01Z

2

This will do the job

Sub CustomSelect()
    Dim i As Long: i = 2
    Do While Range("H" & i).Value = 0
        i = i + 1
    Loop
    Range("H1:H" & i - 1).EntireRow.Select
End Sub

edited Mar 3, 2015 at 19:56

answered Mar 3, 2015 at 19:41

Jeanno

2,8894 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Michael72688 Over a year ago

Thanks for your response. That's very close. But it only selects the cells in column H, not the entire row.

Michael72688 Over a year ago

Okay, here's what I changed: Sub Zeros() Dim i As Long: i = 2 Dim r As Long: r = 1 Do While Range("H" & i).Value = 0 i = i + 1 Loop Rows(r & ":" & i - 1).Select End Sub

Michael72688 Over a year ago

How do I do a carriage return in the comments? Everytime I hit enter it saves the comment instead of going to the next line.

Jeanno Over a year ago

you can't do it in comments, you will need to post an answer, your code (based on my mine) works, also check my edited code Range("H1:H" & i - 1).EntireRow.Select. Please mark the question as answered if you are satisfied with the reply

Michael72688 Over a year ago

I like yours better. Thanks

|

Collectives™ on Stack Overflow

VBA Excel - select all rows with value in a column

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related