+ Reply to Thread
Results 1 to 5 of 5

Last row, last column revisited

  1. #1
    David O. Antillon
    Guest

    Last row, last column revisited

    Hi,

    I have created a table in Excel. To make it easier to read, I need something that changes the cell color every sixth row from column A to J. I want the VBA subroutine to find the last column in a row that has data. What I have below works. However, I want to subroutine to skip to every sixth row, starting with cell A2 until the last row with data. What I have below works, but I don’t want to hard code the 272 (the last row on this particular table). I want the subroutine to continue to that row and stop. I have tried several suggestions made to the newsgroup, but the routines stop at 266 or goes on an infinite loop. Can you recommend some changes?









    Private Sub ColorEverySixthRow()



    ActiveSheet.Range("A2").Select



    ' Dimension variables

    Dim lastCOL As Long

    Dim lastROW As Long



    'Initiate variables

    lastROW = 2

    lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset(0, 1).Column



    'Do Until lastROW = 272



    For lastROW = 2 To 272 Step 6

    With ActiveSheet

    .Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL - 1)).Interior.ColorIndex = 15

    End With

    Next



    End Sub





    I have xl2000 on a windows 98 box.



    Thanks.



    danz98




  2. #2
    Rowan
    Guest

    RE: Last row, last column revisited

    How about:

    Sub ColorEverySixthRow()
    ' Dimension variables

    Dim lastCOL As Integer
    Dim lastROW As Long
    Dim Counter As Long

    'Initiate variables

    lastCOL = Cells(2, Columns.Count).End(xlToLeft).Column
    lastROW = Cells(Rows.Count, 1).End(xlUp).Row

    For Counter = 2 To lastROW Step 6

    Range(Cells(Counter, 1), Cells(Counter, lastCOL)). _
    Interior.ColorIndex = 15
    Next

    End Sub

    Hope this helps
    Rowan

    "David O. Antillon" wrote:

    > Hi,
    >
    > I have created a table in Excel. To make it easier to read, I need something that changes the cell color every sixth row from column A to J. I want the VBA subroutine to find the last column in a row that has data. What I have below works. However, I want to subroutine to skip to every sixth row, starting with cell A2 until the last row with data. What I have below works, but I don’t want to hard code the 272 (the last row on this particular table). I want the subroutine to continue to that row and stop. I have tried several suggestions made to the newsgroup, but the routines stop at 266 or goes on an infinite loop. Can you recommend some changes?
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > Private Sub ColorEverySixthRow()
    >
    >
    >
    > ActiveSheet.Range("A2").Select
    >
    >
    >
    > ' Dimension variables
    >
    > Dim lastCOL As Long
    >
    > Dim lastROW As Long
    >
    >
    >
    > 'Initiate variables
    >
    > lastROW = 2
    >
    > lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset(0, 1).Column
    >
    >
    >
    > 'Do Until lastROW = 272
    >
    >
    >
    > For lastROW = 2 To 272 Step 6
    >
    > With ActiveSheet
    >
    > .Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL - 1)).Interior.ColorIndex = 15
    >
    > End With
    >
    > Next
    >
    >
    >
    > End Sub
    >
    >
    >
    >
    >
    > I have xl2000 on a windows 98 box.
    >
    >
    >
    > Thanks.
    >
    >
    >
    > danz98
    >
    >
    >


  3. #3
    STEVE BELL
    Guest

    Re: Last row, last column revisited

    David,

    I am a little confused: first you say columns A:J than you say "last
    column" in a row.
    But I digress.

    I selected a range and put in a Conditional Format.
    The formulas are easier to write if you are R1C1 mode (Tools > Options)

    Formula1 = Row(RC) = 1
    No Condition
    Formula2 = Row(RC) = 2
    Condition
    Formula3 = Mod(Row(RC)-1,6) = 0
    Condition

    formula1 makes sure that row 1 doesn't get changed
    formula2 makes sure row 2 gets changed
    formula3 changes every 6th row after row2

    Try it out. Than do it with the recorder on and you will get the code.
    Just edit the code to meet your range requirements.

    --
    steveB

    Remove "AYN" from email to respond
    "David O. Antillon" <[email protected]> wrote in message
    news:eryHe.9499$Bx5.8120@trnddc09...
    Hi,
    I have created a table in Excel. To make it easier to read, I need something
    that changes the cell color every sixth row from column A to J. I want the
    VBA subroutine to find the last column in a row that has data. What I have
    below works. However, I want to subroutine to skip to every sixth row,
    starting with cell A2 until the last row with data. What I have below works,
    but I don’t want to hard code the 272 (the last row on this particular
    table). I want the subroutine to continue to that row and stop. I have tried
    several suggestions made to the newsgroup, but the routines stop at 266 or
    goes on an infinite loop. Can you recommend some changes?




    Private Sub ColorEverySixthRow()

    ActiveSheet.Range("A2").Select

    ' Dimension variables
    Dim lastCOL As Long
    Dim lastROW As Long

    'Initiate variables
    lastROW = 2
    lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset(0,
    1).Column

    'Do Until lastROW = 272

    For lastROW = 2 To 272 Step 6
    With ActiveSheet
    .Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL -
    1)).Interior.ColorIndex = 15
    End With
    Next

    End Sub


    I have xl2000 on a windows 98 box.

    Thanks.

    danz98



  4. #4
    David O. Antillon
    Guest

    Re: Last row, last column revisited

    thanks Rowan, your suggestion worked perfectly.

    danz98
    "Rowan" <[email protected]> wrote in message news:[email protected]...
    How about:

    Sub ColorEverySixthRow()
    ' Dimension variables

    Dim lastCOL As Integer
    Dim lastROW As Long
    Dim Counter As Long

    'Initiate variables

    lastCOL = Cells(2, Columns.Count).End(xlToLeft).Column
    lastROW = Cells(Rows.Count, 1).End(xlUp).Row

    For Counter = 2 To lastROW Step 6

    Range(Cells(Counter, 1), Cells(Counter, lastCOL)). _
    Interior.ColorIndex = 15
    Next

    End Sub

    Hope this helps
    Rowan

    "David O. Antillon" wrote:

    > Hi,
    >
    > I have created a table in Excel. To make it easier to read, I need something that changes the cell color every sixth row from column A to J. I want the VBA subroutine to find the last column in a row that has data. What I have below works. However, I want to subroutine to skip to every sixth row, starting with cell A2 until the last row with data. What I have below works, but I don’t want to hard code the 272 (the last row on this particular table). I want the subroutine to continue to that row and stop. I have tried several suggestions made to the newsgroup, but the routines stop at 266 or goes on an infinite loop. Can you recommend some changes?
    >
    >
    >
    >
    >
    >
    >
    >
    >
    > Private Sub ColorEverySixthRow()
    >
    >
    >
    > ActiveSheet.Range("A2").Select
    >
    >
    >
    > ' Dimension variables
    >
    > Dim lastCOL As Long
    >
    > Dim lastROW As Long
    >
    >
    >
    > 'Initiate variables
    >
    > lastROW = 2
    >
    > lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset(0, 1).Column
    >
    >
    >
    > 'Do Until lastROW = 272
    >
    >
    >
    > For lastROW = 2 To 272 Step 6
    >
    > With ActiveSheet
    >
    > .Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL - 1)).Interior.ColorIndex = 15
    >
    > End With
    >
    > Next
    >
    >
    >
    > End Sub
    >
    >
    >
    >
    >
    > I have xl2000 on a windows 98 box.
    >
    >
    >
    > Thanks.
    >
    >
    >
    > danz98
    >
    >
    >


  5. #5
    David O. Antillon
    Guest

    Re: Last row, last column revisited

    J is the last column of the table. I didn't want to hardcode it, since I may use the routine on another table that has more columns. I have been trying out suggestions made by the group and recording with the macro recorder, successfully. On this routine (the one that failed) I crashed my machine twice, so I thought I better ask for help. I'll save your suggestion because I know I'll run into this kind of situation again. Anyway, thanks for your suggestion, and I'll try it next time it comes up.

    danz98
    "STEVE BELL" <[email protected]> wrote in message news:4YzHe.2816$4e6.1901@trnddc04...
    David,

    I am a little confused: first you say columns A:J than you say "last
    column" in a row.
    But I digress.

    I selected a range and put in a Conditional Format.
    The formulas are easier to write if you are R1C1 mode (Tools > Options)

    Formula1 = Row(RC) = 1
    No Condition
    Formula2 = Row(RC) = 2
    Condition
    Formula3 = Mod(Row(RC)-1,6) = 0
    Condition

    formula1 makes sure that row 1 doesn't get changed
    formula2 makes sure row 2 gets changed
    formula3 changes every 6th row after row2

    Try it out. Than do it with the recorder on and you will get the code.
    Just edit the code to meet your range requirements.

    --
    steveB

    Remove "AYN" from email to respond
    "David O. Antillon" <[email protected]> wrote in message
    news:eryHe.9499$Bx5.8120@trnddc09...
    Hi,
    I have created a table in Excel. To make it easier to read, I need something
    that changes the cell color every sixth row from column A to J. I want the
    VBA subroutine to find the last column in a row that has data. What I have
    below works. However, I want to subroutine to skip to every sixth row,
    starting with cell A2 until the last row with data. What I have below works,
    but I don’t want to hard code the 272 (the last row on this particular
    table). I want the subroutine to continue to that row and stop. I have tried
    several suggestions made to the newsgroup, but the routines stop at 266 or
    goes on an infinite loop. Can you recommend some changes?




    Private Sub ColorEverySixthRow()

    ActiveSheet.Range("A2").Select

    ' Dimension variables
    Dim lastCOL As Long
    Dim lastROW As Long

    'Initiate variables
    lastROW = 2
    lastCOL = Sheets("Sheet1").Range("IV1").End(xlToLeft).Offset(0,
    1).Column

    'Do Until lastROW = 272

    For lastROW = 2 To 272 Step 6
    With ActiveSheet
    .Range(.Cells(lastROW, 1), .Cells(lastROW, lastCOL -
    1)).Interior.ColorIndex = 15
    End With
    Next

    End Sub


    I have xl2000 on a windows 98 box.

    Thanks.

    danz98



+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1