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

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
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.
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
VBA Code:
Range([A1], [A:A].Find("#N/A")(0)).Select
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
43,407
Office Version
365
Platform
Windows
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.
 

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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
 

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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.
 

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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.
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,828
Office Version
2016
Platform
Windows
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
 

asddsaasddas

New Member
Joined
Mar 23, 2020
Messages
37
Office Version
2016, 2013
Platform
Windows
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.
 

Forum statistics

Threads
1,089,220
Messages
5,406,928
Members
403,113
Latest member
ms_excel_recal_or_die

This Week's Hot Topics

Top