+ Reply to Thread
Results 1 to 7 of 7

VBA sum column range only if condition met

  1. #1
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    VBA sum column range only if condition met

    Hi,

    I have a sheet which is set up in a way where there are about 200 columns with many rows of data. The rows to use for the code is quite easy because I can just use some simple code for last row.

    for example a simple part of it is below

    Please Login or Register  to view this content.
    My problem lies in the columns. Out of the ~200 columns only 20 (max) will ever be used in the spreadsheet but the 20 used varies from project to project. The columns which are used are displayed in row 4 ("n4:ho4") with a number and the columns which are not used have a "-".

    How would you go about doing the above code so it only needs to use the 20 filled columns rather than the whole range? I'm a simple man who can't work this out! Any help would be appreciated. This would speed up my spreadsheet by about 10x.

  2. #2
    Forum Moderator davesexcel's Avatar
    Join Date
    02-19-2006
    Location
    Regina
    MS-Off Ver
    MS 365
    Posts
    13,474

    Re: VBA sum column range only if condition met

    Welcome to the forum

    Please attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Contributor pipoliveira's Avatar
    Join Date
    08-09-2012
    Location
    Ireland
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    368

    Re: VBA sum column range only if condition met

    I think for what I understood, a simple Conditional Formatting will make the work.
    What will be the criteria to activate your code? Only cells with no data or not in use?
    Cheers,


    * If a reply solved or answered your query/question, you can add reputation to the person by clicking on the * Add Reputation
    * When question is resolved, please mark your thread as SOLVED


    Thanks and regards,
    Filipe Oliveira

  4. #4
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: VBA sum column range only if condition met

    If you add some looping code as similar to that shown below to your existing code to identify only those cells in Row 4 that do not have a "-"

    Please Login or Register  to view this content.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  5. #5
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: VBA sum column range only if condition met

    Alan,

    This is exactly what I need!! Sorry to be basic, but how would you substitute .Range("n13:ho" & lrow).Interior.ColorIndex = 0 to the loop you've suggested above?

  6. #6
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: VBA sum column range only if condition met

    Would it just be

    Please Login or Register  to view this content.

  7. #7
    Forum Contributor
    Join Date
    03-05-2015
    Location
    norwich
    MS-Off Ver
    365
    Posts
    107

    Re: VBA sum column range only if condition met

    I got it now thanks it worked! I just needed another look within for the first and last row

+ 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. counting blank cells in range based on condition using another column
    By TheBlueBell in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-14-2013, 04:58 PM
  2. Concatenate cells in column range if condition is met
    By AlexVen in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-02-2013, 03:30 AM
  3. Replies: 12
    Last Post: 12-28-2012, 07:49 PM
  4. [SOLVED] dynamic range specification (based on column header) in sumproduct multiple condition
    By anand_erin in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-28-2012, 10:16 PM
  5. select a column range based on a condition and copy to another sheet
    By GSCCK in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-02-2012, 10:59 PM
  6. Replies: 6
    Last Post: 05-06-2010, 10:06 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