VBA select range of cells containing formula with value based on first column

[ADingy1]

I have a spreadsheet used to create a file template for another program. Below is an example of my sheet. The data set is in columns A though H and rows 1 through 51. Based on the criteria from other sheets, the rows will be filled top down. Columns A:C will only fill if the appropriate selections have been made using data validation on other worrksheets, but D though H will always contain a value. I need a way to select columns A:H but only the rows that contain data in columns A:C. In the example below, I would want to select $A$1:$H$4. Thank you all for your help.

	A	B	C	D	E	F	G	H
1	"U.S."	33	370	380	400	130	175	62.1 psi
2	.5	.8	.25	0	360	32	345
3	.25	.9	.15	32	345	42	344
4	.5	.85	.2	42	344	65	310
5				65	310	65	-520
6				65	-520	65	-520

<tbody>
</tbody>

****** id="cke_pastebin" style="position: absolute; top: 118px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

.1

.25

.2

<tbody>
</tbody>

</body>

 

[Fluff]

Hi & welcome to MrExcel
How about

Sub chk()
   Dim UsdRws As Long
   UsdRws = Range("A:A").find("*", , xlValues, , , xlPrevious, , , False).Row
   Range("A1:H" & UsdRws).Select
End Sub

 

[ADingy1]

Hi & welcome to MrExcel
How about

Sub chk()
   Dim UsdRws As Long
   UsdRws = Range("A:A").find("*", , xlValues, , , xlPrevious, , , False).Row
   Range("A1:H" & UsdRws).Select
End Sub

It is selecting all cells in A1:H. I think UsdRws is either reading the lower rows because the last set of 4 cells in them are used or it is looking at the formula they contain.

 

[Fluff]

Ok, how about

Sub chk()
   Dim UsdRws As Long
   UsdRws = Range("A:A").find("", , xlValues, , , xlNext, , , False).Row
   Range("A1:H" & UsdRws - 1).Select
End Sub

 

[ADingy1]

Thanks for the fast responses but again no dice. I looked at the original code you posted again and it is selecting A1:H52, however Row 52 contains no data. The new code is selecting A1:H51. Looking at it, it seems like it should have only selected A1:H50, so I'm not sure what's going on. With A1:A51 containing the formulas A1='Sheet 3'!C60 and A2='Sheet 3'!C63 to A51='Sheet 3'!C112 I don't know why Row 52 is being selected in the first place though. If you could give me a very quick understanding of why I would appreciate it as well. I've learned what I know about VBA from reading the macros I record so I know that I'm severely lacking knowledge. Thanks for the response again.

 

[Fluff]

This line

UsdRws = Range("A:A").find("", , xlValues, , , xlNext, , , False).Row

should find the 1st cell in col A that has no value & assigns that row number to UsdRws.
As you don't want to select that row, the next line UsdRws -1, which should then select the correct range.
What is in C112 on sheet 3? If it looks blank, check that there isn't a space in there

 

[ADingy1]

My referenced cells on sheet 3 reference other cells on sheet 3 which combine information from sheet 2 and sheet 9 based on input information from sheet 1 using an intermediary table (the one my reference cells look at on sheet 3) and then perform some basic operations. I'm stepping it though a number of sheets because I thought I would be the only one using it, but now I'm trying to automate it. I might be able to remove my reference table and change my references to sheet based, but I'm not sure if that will help though, since ultimately it all points back to an input sheet.

 

[ADingy1]

I found my space. It works now and you were right, it was a space. Thank you!

 

[Fluff]

Glad to help & thanks for the feedback