macro help

[david graham]

The code below sorts a list & then formats only the visible rows. This there a line i can add that will keep it from trying to format when there are no visible rows in the selection?

will "If Not rng Is Nothing Then" work & if so where do i need to put it?

'FORMAT LABOR ITEMS
  Sheets("LIST").Select
 Range("R1").Select
    Selection.AutoFilter Field:=1, Criteria1:="6"
Range("b1").Select
Set r = ThisWorkbook.Worksheets("LIST").Range("B14:b30000").SpecialCells(xlCellTypeVisible)
   With r.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        r.HorizontalAlignment = xlLeft
        r.VerticalAlignment = xlTop
        r.WrapText = True
        r.Font.ColorIndex = 2
        r.RowHeight = 30.75
    End With
Set r = ThisWorkbook.Worksheets("LIST").Range("C14:C30000").SpecialCells(xlCellTypeVisible)
   With r.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        r.HorizontalAlignment = xlLeft
        r.VerticalAlignment = xlTop
        r.WrapText = True
        r.Font.ColorIndex = 1
        r.RowHeight = 30.75
    End With
Set r = ThisWorkbook.Worksheets("LIST").Range("D14:E30000").SpecialCells(xlCellTypeVisible)
   With r.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        r.HorizontalAlignment = xlCenter
        r.VerticalAlignment = xlCenter
        r.WrapText = True
        r.Font.ColorIndex = 1
        r.RowHeight = 30.75
    End With

 

[crimson_b1ade]

Hi David, my buddy...my pal...you could try using a variable to pick up the last row instead of going all the down to row 30,000. see below example. Items in bold face red are what i changed. By the by, you don't need to Select stuff to work with them in vba.:

Sub DavidLikesToFormat()
 
'FORMAT LABOR ITEMS
 
Dim LastRow As Long
 
Sheets("LIST").Select
Range("R1").Select
Selection.AutoFilter Field:=1, Criteria1:="6"
Range("b1").Select
 
LastRow = Sheets("LIST").Range("B" & Rows.Count).End(xlUp).Row
 
Set r = ThisWorkbook.Worksheets("LIST").Range("B14:B" & LastRow).SpecialCells(xlCellTypeVisible)
 
   With r.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        r.HorizontalAlignment = xlLeft
        r.VerticalAlignment = xlTop
        r.WrapText = True
        r.Font.ColorIndex = 2
        r.RowHeight = 30.75
    End With
 
Set r = ThisWorkbook.Worksheets("LIST").Range("C14:C" & LastRow).SpecialCells(xlCellTypeVisible)
 
   With r.Interior
        .ColorIndex = 2
        .Pattern = xlSolid
        r.HorizontalAlignment = xlLeft
        r.VerticalAlignment = xlTop
        r.WrapText = True
        r.Font.ColorIndex = 1
        r.RowHeight = 30.75
    End With
 
Set r = ThisWorkbook.Worksheets("LIST").Range("D14:E" & LastRow).SpecialCells(xlCellTypeVisible)
 
   With r.Interior
        .ColorIndex = 15
        .Pattern = xlSolid
        r.HorizontalAlignment = xlCenter
        r.VerticalAlignment = xlCenter
        r.WrapText = True
        r.Font.ColorIndex = 1
        r.RowHeight = 30.75
    End With
 
End Sub