PDA

View Full Version : Limit amount of rows formatted



john3j
10-01-2009, 07:13 PM
hey guys how can I modify this code to run on without formatting down to 64450 rows? It currently goes all the way down the sheet and the file is way to huge to send to anyone without clogging up their inbox? I would like for it to look at the last row that has data in the third column and then format up the sheet from there. Thanks!

Sub blah()
LastRow = Cells(Rows.Count, 3).End(xlUp).Row
For rw = LastRow To 2 Step -1
If Cells(rw, 3).Value <= "08" Or InStr(Cells(rw, 25).Value, "*If chosen") > 0 Then
Cells(rw, 1).EntireRow.delete
Else
Select Case Cells(rw, 25).Value
Case "Contract Awarded"
Rows(rw).Interior.ColorIndex = 35
Rows(rw).Font.ColorIndex = 1
Case "Part A Held"
Rows(rw).Interior.ColorIndex = 34
Rows(rw).Font.ColorIndex = 1
Case "Part B Accepted"
Rows(rw).Interior.ColorIndex = 38
Rows(rw).Font.ColorIndex = 1
Case "Part B Submitted"
Rows(rw).Interior.ColorIndex = 36
Rows(rw).Font.ColorIndex = 1
Case "Planning"
Rows(rw).Interior.ColorIndex = 2
Rows(rw).Font.ColorIndex = 1
Case "Postponed"
Rows(rw).Interior.ColorIndex = 39
Rows(rw).Font.ColorIndex = 1
End Select
Cells(rw, 1).Resize(, 27).Cut tgt
End If
End Sub

Paul_Hossler
10-02-2009, 05:18 AM
"Cells" without a qualifing object refers to the entire worksheet

My style (personal preferance) is to use .CurrentRegion, e.g.


Worksheets("ABCDEF").Cells(1,1).CurrentRegion.Rows.Count



Option Explicit
Sub blah2()
Dim LastRow As Long, rw As Long

With ActiveSheet 'or Worksheets ("ABCDEF")

LastRow = .Cells(.Rows.Count, 3).End(xlUp).Row

For rw = LastRow To 2 Step -1
If .Cells(rw, 3).Value <= "08" Or InStr(.Cells(rw, 25).Value, "*If chosen") > 0 Then
.Cells(rw, 1).EntireRow.Delete
Else
Select Case .Cells(rw, 25).Value
Case "Contract Awarded"
.Rows(rw).Interior.ColorIndex = 35
.Rows(rw).Font.ColorIndex = 1
Case "Part A Held"
.Rows(rw).Interior.ColorIndex = 34
.Rows(rw).Font.ColorIndex = 1
Case "Part B Accepted"
.Rows(rw).Interior.ColorIndex = 38
.Rows(rw).Font.ColorIndex = 1
Case "Part B Submitted"
.Rows(rw).Interior.ColorIndex = 36
.Rows(rw).Font.ColorIndex = 1
Case "Planning"
.Rows(rw).Interior.ColorIndex = 2
.Rows(rw).Font.ColorIndex = 1
Case "Postponed"
.Rows(rw).Interior.ColorIndex = 39
.Rows(rw).Font.ColorIndex = 1
End Select
' Cells(rw, 1).Resize(, 27).Cut tgt ' not sure why
End If
Next
End With
End Sub


Didn't really test it, but just wanted to check LastRow as not at the bottom

Paul