Consulting

Results 1 to 2 of 2

Thread: Limit amount of rows formatted

  1. #1
    VBAX Contributor
    Joined
    Mar 2009
    Location
    Indiana
    Posts
    113
    Location

    Question 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

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    "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
  •