+ Reply to Thread
Results 1 to 3 of 3

Count last non blank column and apply to formula

  1. #1
    Registered User
    Join Date
    07-18-2007
    Posts
    3

    Count last non blank column and apply to formula

    Need a macro that counts from cell A4 until blank column. So lets say the blank column is L4 (11 nonblanks, 1 blank). I need to then offset that by 1 row down and column back so it is K5, I than need to apply that to the formula below and to: lastrow = cells (rows.count, instead of the 1 like it is below i need this to read 11 (because column K is the count of non blank columns = 11). The reason for this is because I have a Pivot table that gets wider and longer at least every month and I need the last column in the pivot table multiplied by $B$1.


    Code:
    Sub Button1_Click()
    Dim lastrow As Long
    lastrow = Cells(Rows.Count, 1).End(xlUp).Row
    Range("a5").Formula = "= """"K5""" * $b$1"
    Range("a5").AutoFill Destination:=Range("a5:a" & lastrow)
    End Sub

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200
    This will get the cell's row & column, but I am getting an error with your formula

    Please Login or Register  to view this content.
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    07-18-2007
    Posts
    3
    OK, I found it out in case anyone else could use it.

    With Sheets("Company").Activate
    With Range("b5", Range("b" & Rows.Count).End(xlUp))
    x = .Cells(1).End(xlToRight).Offset(1).Address(0, 0)
    lastrow = Cells(Rows.Count, 4).End(xlUp).Row
    Range("b6").Formula = "=" & x & "*$c$1"
    Range("b6").AutoFill Destination:=Range("b6:b" & lastrow)
    lastrow = Cells(Rows.Count, 2).End(xlUp).Row
    Range("c6").Formula = "=$C$2 * b6"
    Range("c6").AutoFill Destination:=Range("c6:c" & lastrow)

    Range("b5").End(xlDown).Select
    Selection.Copy
    Range("c3").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Range("c5").End(xlDown).Select
    Selection.Copy
    Range("c4").Select
    Selection.pastespecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    End With
    End With

+ 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