VBA coding - SET RNG

[btsboarder]

Hi All,

I am trying to have my VBA code grab all the information in the relevant range, but not pick up any empty lines, i.e stop when there is no more information. This is the code I have right now.

Set rng = Nothing
Set rng = Range("B5:H31").SpecialCells(xlCellTypeVisible)

So say my information only goes to row 20, I don't want it to continue until row 31. I have it set up to paste the information into an email, so I want to cut down on white noise.

Thanks!

 

[pplstuff]

Maybe:
set rng = range("B5:H" & range("B5").end(xldown).row)

Cell type visible works with hidden rows, not just empty rows. Using xldown will be the equivalent of selecting B5 and then hitting Ctrl + down arrow.

 

[btsboarder]

So I have a range that has formulas in rows B:H to row 30. So the control down will select the formulas. They pull if information is entered in column A. So if now information is given in column A, it'll be blank. But that code still thinks control down to the end. Thoughts?

 

[Jonmo1]

pplstuff was on the right track, but now that we know column B is filled with formulas referring to column A, then we can use column A to find the last row

Try
set rng = range("B5:H" & range("A5").end(xldown).row)

 

[btsboarder]

thats the same code?

 

[Jonmo1]

lol, I meant to change the B to an A..
Edited the post.

 

[btsboarder]

Got it to work, using the start of the range for my number inputs in column A. Thanks guys/gals!