+ Reply to Thread
Results 1 to 2 of 2

Finding last row till data is there and calculating

Hybrid View

  1. #1
    Registered User
    Join Date
    01-15-2009
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    8

    Finding last row till data is there and calculating

    Hi,

    I has a worksheet which gets data for a week range. My coloumns are fixed i.e one week range. But Rows can be differed and i need to calculate avg and based on avg i need to color the rows.

    My format is like this :
    A B C D E F G H
    1
    2
    3
    4
    5
    X

    H is the coloumn where i take the avg. I have written VBA code by hard coding the row value, but in general it will vary. I am not sure how to find the last row and then calculate Avg.

    I am adding the code which i did for constants Rows.

    ActiveCell.FormulaR1C1 = "=AVERAGE(RC[-7]:RC[-1])"
        Range("H2").Select
        Selection.AutoFill Destination:=Range("H2:H90")
        Range("H2:H90").Select
        Columns("H:H").Select
        Selection.NumberFormat = "0"
        
        Dim Rng As Range, cell As Range
    Set Rng = Range("A2:G90")
      Rng.Interior.ColorIndex = xlNone
        
        For Each cell In Rng
            If cell.Value < Range("H" & cell.Row) * 0.2 Then
                cell.Interior.ColorIndex = 3
            ElseIf cell.Value > Range("H" & cell.Row) * 0.2 And cell.Value < Range("H" & cell.Row) * 0.5 Then
                cell.Interior.ColorIndex = 6
            End If
    Next cell

    Could you please help me out in writing VBA code for getting last row and do the calculation instead of hardcoding the row range.

    Thanks & Regards,
    Pradeep

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016 / 365
    Posts
    3,454

    Re: Finding last row till data is there and calculating

    Hi,

    To locate the last occupied row (in column A) you could use
    lastrow = Cells(Rows.Count, 1).End(xlUp)
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

+ 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