Hello, I am creating a userform where depending on which option selected in the combobox different ranges should be selected. The ranges are taken from a long list with columns from A to E.
A is filled with dates.
It looks something like this:
If i am choosing option "Hello" in the combobox, I want rows with the text "hello" in the range B5:E8 to be selected, that is from the last row A is filled including all rows with "hello".
Down below is my current code. Do you have any suggestions?
Dim lrA As Long
Dim lrB As Long
Dim rng As Range
lrA = Cells(Rows.Count, "A").End(xlUp).Row
lrB = Cells(Rows.Count, "L").End(xlUp).Row
Set rng = Range(Cells(lrA, "B"), Cells(lrB, "L"))
Dim rngB As Range
Dim cell As Range
Dim col As Range
Dim copiedRange As Range
Dim r As Integer
Set col = Cells(Rows.Count, "B").End(xlUp)
r = 0
Set rngA = Range("B:B", col)
If cbDep.Text = "Hello" Then
For Each cell In rngA
If cell.Value = "Hello" Then
If r = 0 Then
Set copiedRange = cell.EntireRow
r = 1
Else
Set copiedRange = Union(copiedRange, cell.EntireRow)
End If
End If
Next cell
If r = 1 Then
copiedRange.Copy
End If
A is filled with dates.
It looks something like this:
If i am choosing option "Hello" in the combobox, I want rows with the text "hello" in the range B5:E8 to be selected, that is from the last row A is filled including all rows with "hello".
Down below is my current code. Do you have any suggestions?
Dim lrA As Long
Dim lrB As Long
Dim rng As Range
lrA = Cells(Rows.Count, "A").End(xlUp).Row
lrB = Cells(Rows.Count, "L").End(xlUp).Row
Set rng = Range(Cells(lrA, "B"), Cells(lrB, "L"))
Dim rngB As Range
Dim cell As Range
Dim col As Range
Dim copiedRange As Range
Dim r As Integer
Set col = Cells(Rows.Count, "B").End(xlUp)
r = 0
Set rngA = Range("B:B", col)
If cbDep.Text = "Hello" Then
For Each cell In rngA
If cell.Value = "Hello" Then
If r = 0 Then
Set copiedRange = cell.EntireRow
r = 1
Else
Set copiedRange = Union(copiedRange, cell.EntireRow)
End If
End If
Next cell
If r = 1 Then
copiedRange.Copy
End If