-
Limit amount of rows formatted
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
-
"Cells" without a qualifing object refers to the entire worksheet
My style (personal preferance) is to use .CurrentRegion, e.g.
[vba]
Worksheets("ABCDEF").Cells(1,1).CurrentRegion.Rows.Count
[/vba]
[vba]
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
[/vba]
Didn't really test it, but just wanted to check LastRow as not at the bottom
Paul
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules