How do I select the number of rows until i reach a certain value

asddsaasddas

Board Regular
Joined
Mar 23, 2020
Messages
60
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
For instance, I have column A populated with different random words. So for example at row 6, I have a "#N/A" instead of a random word. I want to be able to select all the values above row 6. Technically the "#N/A" is like a stopper to prevent the system from selecting it or anything below it. Is there any way to select all the values above the "#N/A". The number of random words are not definite. Thus, I may have #N/A" to appear at row 13 and the values from row 1 to 12 can be selected. I want the code to be flexible and can adapt to "#N/A" in any row as long as it is in column A.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
VBA Code:
Range([A1], [A:A].Find("#N/A")(0)).Select
 
Upvote 0
VBA Code:
Range([A1], [A:A].Find("#N/A")(0)).Select
1585102629041.png

This is my current code, what/how do i change it such that it selects only the data above "#N/A" using your code?
 
Upvote 0
This is my current code
For the future, you are best to post any code in a form that helpers can easily copy in case they want to test before making a suggestion. My signature block below has help with that.
 
Upvote 0
For the future, you are best to post any code in a form that helpers can easily copy in case they want to test before making a suggestion. My signature block below has help with that.
VBA Code:
Sub setnameforrange2()
    
'declare variables
Dim startCell2 As Range
Dim lastRow2 As Long
Dim lastCol2 As Long
Dim ws2 As Worksheet

'set objects
Set ws2 = ThisWorkbook.Worksheets("Sheet19")
Set startCell2 = Range("AE5")

'Find last row of data
lastRow2 = ws2.Cells(ws2.Rows.Count, startCell2.Column).End(xlUp).Row
lastCol2 = ws2.Cells(startCell2.Row, 32)
'lastCol = ws.Cells(startCell.Row, ws.Columns.Count).End(xlToLeft).Column


'select dymanic range of data
ws2.Range(startCell2, ws2.Cells(lastRow2, 31)).Select

'set name for the range
ws2.Range(startCell2, ws2.Cells(lastRow2, 31)).Name = "testdata2"


End Sub

Noted, Here is my code.
 
Upvote 0
The code I posted covers what you described you wanted in your original post.

If you now want to select from startCell2 to the cell above "#N/A" in the startCell2 column, just adjust the code I posted accordingly :
VBA Code:
Range(startCell2, Columns(startCell2.column).Find("#N/A")(0)).Select
 
Upvote 0
The code I posted covers what you described you wanted in your original post.

If you now want to select from startCell2 to the cell above "#N/A" in the startCell2 column, just adjust the code I posted accordingly :
VBA Code:
Range(startCell2, Columns(startCell2.column).Find("#N/A")(0)).Select
Thank you, but I failed to mention there might or might not be "#N/A" in the column. Thus, if "#N/A" is not found, it should select everything in the column. The amount of rows in the column are not fixed thus, is there a code to find out if "#N/A" is present, if present select everything above it, if not select the whole column. Any help is appreciated, sorry for not being clear on my first post.
 
Upvote 0
The code I posted covers what you described you wanted in your original post.

If you now want to select from startCell2 to the cell above "#N/A" in the startCell2 column, just adjust the code I posted accordingly :
VBA Code:
Range(startCell2, Columns(startCell2.column).Find("#N/A")(0)).Select
To clear any doubts,

I have column A populated with random words. The number of rows are not definite and might change constantly based on the data retrieved. I want the VBA code to be able to count the number of rows and select them. After selecting them, I want to be able to give a name to the name range. The problem is that if there is a "#N/A" as one of the cell values in column A. The code should select only everything above the "#N/A" as the range and allow me to give a name to the name range. Another factor to consider is, there might or might not be "#N/A" as the cell value in column A. If there is no "#N/A" in column A, it should select everything and allow me to name the name range. If there is "#N/A" present in row 12, it should select everything above row 12 and allow me to name the name range. Any help is appreciated. Thank you in advanced.
 
Upvote 0
VBA Code:
On Error Resume Next
Set rng = Range(startCell2, Columns(startCell2.Column).Find("#N/A")(0))
If rng Is Nothing Then Set rng = Range(startCell2, Cells(lastrow2, startCell2.Column))
On Error GoTo 0
rng.Select
 
Upvote 0
H
VBA Code:
On Error Resume Next
Set rng = Range(startCell2, Columns(startCell2.Column).Find("#N/A")(0))
If rng Is Nothing Then Set rng = Range(startCell2, Cells(lastrow2, startCell2.Column))
On Error GoTo 0
rng.Select
Hi, is working now thanks. May I ask if "#N/A" was replaced by a number will it still work. Tried it at my end and it doesn't.
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,720
Members
448,294
Latest member
jmjmjmjmjmjm

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