Select last row with data in

[berty2000]

Need to search down rows in range B5:M6000 to find the last row with data in then select the next row down from column B to M and colour yellow.

Seems simple but cant seem to get result

Sub test()


'   Select the range: Last row - Last Column
    Range("B5:M6000").Select
Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0).Select
    
End Sub

 

[VoG]

Try

Sub test()
Range("B" & Rows.Count).End(xlUp).Offset(1).Resize(, 12).Interior.ColorIndex = 6
End Sub

 

[AlphaFrog]

Another method.

[color=darkblue]Sub[/color] test()


[color=darkblue]Dim[/color] Lastrow [color=darkblue]As[/color] [color=darkblue]Long[/color]


Lastrow = Range("B5:M6000").Find(What:="*", After:=Range("B5"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Range("B" & Lastrow + 1).Resize(, 12).Interior.Color = vbYellow


[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[berty2000]

That is nearly perfect for what I need except sometimes there is no data in the B column but there is in say the H column so I need it to search across the range for the last row thats my stumbling block VoG

 

[VoG]

Similar to AlphaFrog's

Sub test()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("B" & LR + 1).Resize(, 12).Interior.ColorIndex = 6
End Sub

 

[berty2000]

Another method.

[COLOR=darkblue]Sub[/COLOR] test()


[COLOR=darkblue]Dim[/COLOR] Lastrow [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]


Lastrow = Range("B5:M6000").Find(What:="*", After:=Range("B5"), LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


Range("B" & Lastrow + 1).Resize(, 12).Interior.Color = vbYellow


[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

That works perfectly, thank you very much wish I ask early spent last 2 hour trying to figure this out

 

[berty2000]

Similar to AlphaFrog's

Sub test()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Range("B" & LR + 1).Resize(, 12).Interior.ColorIndex = 6
End Sub

This too is perfect thank you both guys

 

[berty2000]

I have added

 LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).row
Range("B" & LR + 1).Resize(, 12).Interior.ColorIndex = 6      
Range("B" & LR + 1).Resize(, 12).Cells.MergeCells = True

So it merges the yellow cells into one cell but how do i make that the active cell or selection so i can resize it pull text into it etc I have tried

'Format cell for Title Placement 
   Range("B" & ActiveCell.row).Select
    ActiveCell.HorizontalAlignment = xlCenter
    ActiveCell.Value = Department

 

[VoG]

Try

Sub test()
Dim LR As Long
LR = Cells.Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
With Range("B" & LR + 1).Resize(, 12)
    .Interior.ColorIndex = 6
    .HorizontalAlignment = xlCenterAcrossSelection
    .Resize(, 1).Value = "Department"
End With
End Sub

 

[berty2000]

Perfect again I will just call you God !