+ Reply to Thread
Results 1 to 10 of 10

VBA -- Need help in Summing values from Visible Cells (row-wise)

  1. #1
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    VBA -- Need help in Summing values from Visible Cells (row-wise)

    Dear all,

    Requesting any help on Summing values of visible cells (row-wise)

    PFA for the excel sheet.

    Input sheet : Data which is the input to the macro. (will always be dynamic)
    Unique Names sheet : Has list of unique names.
    Output sheet : Presently what am able to achieve.
    Final sheet : Expected Output

    Please Login or Register  to view this content.
    Appreciate if you could also help with writing this entire code efficiently.

    Thanks,
    Vinod Krishna
    Attached Files Attached Files

  2. #2
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Dear Bob,

    Your solution is efficient but gives partially correct results

    Correction in Output:
    A -> FW03 -> 0
    B -> FW01 -> 0
    B -> FW02-> 2
    B-> FW03-> 2

    Just to inform, the data at the year end will have ~52/53 FWs (columns) and 1000 rows.

    Appreciate all your help

    Thanks

  4. #4
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    This is one of those classic "you should do it different" moments. Have you considered entering data in a 3 column format?

    Like:

    NAME - FW# - Value
    A 01 4
    B 01 1
    A 02 3
    B 02 2


    Excel is very good at expanding vertically - this format will be very easy to summarize. Your "Final" sheet is an example of a pivot table.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

  5. #5
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    My input sheet is not being input manually but output from an script. This will eventually have 53 columns and close to 1000 rows. There will be blanks in between.

    This needs to be converted to
    Name -- FW -- Hours

    No manual intervention.

    Once I get it in this format, yes it can summarized like a Pivot table.
    Last edited by Vinod Krishna.C; 08-05-2014 at 02:43 PM.

  6. #6
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Quote Originally Posted by Vinod Krishna.C View Post
    Dear Bob,

    Your solution is efficient but gives partially correct results

    Correction in Output:
    A -> FW03 -> 0
    B -> FW01 -> 0
    B -> FW02-> 2
    B-> FW03-> 2
    I don't think so, I think your predicted results are wrong. There are two A/FW03 entries, one with a value of 0, one with a value of 1. That sums to 1 in my book.

  7. #7
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Input:
    Name FW01 FW02 FW03
    A 1 2 -
    B - 2 2
    A - 2 1

    Expected Output:
    Name FW Hours
    A FW01 1
    A FW02 4
    A FW03 1
    B FW01 0
    B FW02 2
    B FW03 2

    Bob's Output:
    Name FW Hours
    A FW01 1
    A FW02 4
    A FW03 1
    B FW01 1
    B FW02 4
    B FW03 1

    PFA for reference.
    Attached Files Attached Files

  8. #8
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Please Login or Register  to view this content.

  9. #9
    Forum Guru Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,286

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    You could also pull Input data into Power Query and unpivot it. Save the results to the workbook, and a simple pivot will get your rewsults.

  10. #10
    Forum Contributor
    Join Date
    06-05-2013
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2016
    Posts
    110

    Re: VBA -- Need help in Summing values from Visible Cells (row-wise)

    Bob,

    Your code works like a charm. Thanks a lot.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Adding a Row Wise Formula in Filtered Special Visible cells only
    By e4excel in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-01-2013, 09:46 AM
  2. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  3. [SOLVED] Copy/Pasting Values from visible cells (using autofilter) to visible cells
    By evakotsi in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2012, 07:49 AM
  4. Copy Visible cells and paste values only to visible target cells
    By wotadude in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2010, 04:09 AM
  5. summing visible cells only
    By loner2003 in forum Excel General
    Replies: 1
    Last Post: 02-11-2010, 04:17 PM

Tags for this Thread

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