Select multiple ranges based on cell value

Question

Hi,

I need to select multiple ranges in a sheet, based on a cell value in "D" column.

The values in "D" column are: Shelf1, Shelf 2, Shelf2........etc.

I found this code, but it's working only with one value.

Sub SelectShelf1()

 Dim nRow As Long

 Dim nStart As Long, nEnd As Long

 ' Figure out where the "Shelf 1" data starts.

 For nRow = 1 To 65536

 If Range("D" & nRow).Value = "Shelf 1" Then

 nStart = nRow

 Exit For

 End If

 Next nRow

 ' Figure out where the "Shelf 1" data ends.

 For nRow = nStart To 65536

 If Range("D" & nRow).Value <> "Shelf 1" Then

 nEnd = nRow

 Exit For

 End If

 Next nRow

 nEnd = nEnd - 1

 Range("A" & nStart & ":G" & nEnd).Select

 'End Sub

Call BorderLine

End Sub

I tried to make a "Call" macro, to call SelectShelf1, SelectShelf2, SelectShelf3...etc,but it's not working.

Can the above macro work for multiple ranges?

"Call BorderLine" puts a border around the selection.

I need this macro to put a border around all values ranges I specify.

Values are in "D' column, and the border range is "A:D"

Can this be done?

Thanks.

Answer 1

try this

Sub TrySelectShelf2_test()

    '''''''''''''''select range based on cell value "Shelf 2"

 On Error GoTo errh        '<<< new line Dim nRow As Long

 Dim nStart As Long, nEnd As Long

 ' Figure out where the "Shelf 2" data starts.

 For nRow = 1 To 65536

 If Range("D" & nRow).Value = "Shelf 2" Then

 nStart = nRow

 Exit For

 End If

 Next nRow

 ' Figure out where the "Shelf 2" data ends.

 For nRow = nStart To 65536

 If Range("D" & nRow).Value <> "Shelf 2" Then

 nEnd = nRow

 Exit For

 End If

 Next nRow

 nEnd = nEnd - 1

 Range("A" & nStart & ":G" & nEnd).Select

 'End Sub

Call BorderLine

Exit Sub          '<<< new lineerrh:            '<<< new lineMsgBox "found nothing"         '<<< new lineEnd Sub

Answer 2

Hi,

It's not working.

The second code put a border around all entries, and the first one draw random borders.

Also, I need the border to be from A to G, even if the criteria "Shelf1" is in "D".

Thanks.

Answer 3

ok,

sort your data in ascending order

and then try this.

Sub macro02()

Const myCol As String = "D"    '<<< shelf names in column D, changet = 1    '<<< data starts from row 1, changeDim r As Long, i As Long

r = Cells(Rows.Count, myCol).End(xlUp).Row

Range(Cells(t, "A"), Cells(t, "D")).Borders(xlEdgeTop).LineStyle = xlContinuous

For i = t + 1 To r - 1

If Cells(i, 1) <> Cells(i + 1, 1) Then

Range(Cells(i + 1, "A"), Cells(i + 1, "D")).Borders(xlEdgeTop).LineStyle = xlContinuous

End If

Next

End Sub

or...

Sub macro03()

Const myCol As String = "D"    '<<< shelf names in column D, changet = 1    '<<< data starts from row 1, changeDim r As Long, i As Long

r = Cells(Rows.Count, myCol).End(xlUp).Row

Range(Cells(t, "A"), Cells(t, "D")).Borders(xlEdgeTop).LineStyle = xlContinuous

For i = t + 1 To r - 1

If Cells(i, 1) <> Cells(i + 1, 1) Then

Range(Cells(i + 1, "A"), Cells(i + 1, "D")).Borders(xlEdgeTop).LineStyle = xlContinuous

End If

Next

Range(Cells(t, "A"), Cells(r, "D")).Borders(xlEdgeRight).LineStyle = xlContinuousRange(Cells(t, "A"), Cells(r, "D")).Borders(xlEdgeLeft).LineStyle = xlContinuousRange(Cells(r, "A"), Cells(r, "D")).Borders(xlEdgeBottom).LineStyle = xlContinuousEnd Sub

Answer 4

Hi TasosK,

Thank you, but I need to add a macro at the end of another one. So I need only a macro

I have a table like this:

_____________________________________________________

some data......some data.....shelf1.....some data....

some data......some data.....shelf1.....some data....

some data......some data.....shelf1.....some data....

______________________________________________________

some data......some data.....shelf2.....some data....

some data......some data.....shelf2.....some data....

______________________________________________________

some data......some data.....shelf3.....some data....

some data......some data.....shelf3.....some data....

some data......some data.....shelf3.....some data....

______________________________________________________

I need the macro to put a border around shelf1 range, another one around shelf2 range and another one around shelf3. basically I want to make some kind groups to be easy to read.

Can this be done?

Thanks.

Answer 5

Hi,

try this..

use, Data Validation List

add all: Shelf 1,Shelf 2,Shelf 3.. in a list

next, use one cell e.g cell D1 as drop down menu,

so, if you select from the list the name, 'Shelf 1' , then via code, do this..ect

Sub macro01()

Dim ws As Worksheet

Dim X As String

Set ws = ActiveSheet

X = ws.Range("D1").Value

Select Case X

Case "Shelf 1"

'your code here

Case "Shelf 2"

'your code here

Case "Shelf 3"

'your code here

Case "Shelf 4"

'your code here

Case Else

'do nothing

End Select

End Sub