+ Reply to Thread
Results 1 to 14 of 14

Summing Blank Rows in a file

Hybrid View

BryceVBA Summing Blank Rows in a file 03-16-2017, 02:21 PM
k64 Re: Summing Blank Rows in a... 03-16-2017, 02:42 PM
BryceVBA Re: Summing Blank Rows in a... 03-16-2017, 03:00 PM
jaslake Re: Summing Blank Rows in a... 03-16-2017, 03:20 PM
BryceVBA Re: Summing Blank Rows in a... 03-16-2017, 04:03 PM
jaslake Re: Summing Blank Rows in a... 03-16-2017, 04:13 PM
BryceVBA Re: Summing Blank Rows in a... 03-16-2017, 05:18 PM
jaslake Re: Summing Blank Rows in a... 03-16-2017, 05:30 PM
jaslake Re: Summing Blank Rows in a... 03-16-2017, 04:53 PM
BryceVBA Re: Summing Blank Rows in a... 03-16-2017, 05:29 PM
jaslake Re: Summing Blank Rows in a... 03-16-2017, 05:37 PM
k64 Re: Summing Blank Rows in a... 03-16-2017, 03:37 PM
BryceVBA Re: Summing Blank Rows in a... 03-16-2017, 04:11 PM
k64 Re: Summing Blank Rows in a... 03-16-2017, 04:15 PM
  1. #1
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Summing Blank Rows in a file

    Hello all,

    I'm trying to sum most of the blank rows in the attached file from column H to column T. I've used the following code to complete the task with a different file. With this code I only had to sum two tables. With the attached file I need to sum like 50 tables. So I wanted to see if anyone could help me with some code that would do this more efficiently than me creating 100's of last row variables to find the blank rows.

    LastRow1 = Range("A5").End(xlDown).Row
    LastRow2 = Range("A4").End(xlDown).End(xlDown).End(xlDown).Row
    LastRow3 = Range("A6").End(xlDown).End(xlDown).Row
    
    
    'Sum and format table1
    With Worksheets("Hours")
        .Range("H" & LastRow1 + 1 & ":T" & LastRow1 + 1).Formula = "=SUM(H4:H" & LastRow1 & ")"
    End With
    Range("H" & LastRow1 + 1 & ":T" & LastRow1 + 1).Select
        Selection.Font.Bold = True
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    
    'Sum and format table2
    'On Error is here in case there isn't a second table
    On Error Resume Next
    With Worksheets("Hours")
        .Range("H" & LastRow2 + 1 & ":T" & LastRow2 + 1).Formula = "=SUM(H" & LastRow3 & " :H" & LastRow2 & ")"
    End With
        Range("H" & LastRow2 + 1 & ":T" & LastRow2 + 1).Select
        Selection.Font.Bold = True
        Selection.Borders(xlDiagonalDown).LineStyle = xlNone
        Selection.Borders(xlDiagonalUp).LineStyle = xlNone
        Selection.Borders(xlEdgeLeft).LineStyle = xlNone
        With Selection.Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With Selection.Borders(xlEdgeBottom)
            .LineStyle = xlDouble
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThick
        End With
        Selection.Borders(xlEdgeRight).LineStyle = xlNone
        Selection.Borders(xlInsideVertical).LineStyle = xlNone
        Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    On Error GoTo 0
    
    
    End Sub
    EDIT: UPDATED ATTACHMENT XLSM AND VBA CODE TO CURRENT SUGGUSTION
    Attached Files Attached Files
    Last edited by BryceVBA; 03-16-2017 at 03:08 PM.

  2. #2
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Summing Blank Rows in a file

    Try this:

    Sub sums()
    For x = 5 To ActiveSheet.UsedRange.Rows.Count
        If Cells(x, 8) = "" And Cells(x - 1, 8) <> "" Then
            If Cells(x - 2, 8) = "" Then
                Cells(x, 8).FormulaR1C1 = "=R[-1]C[0]"
            Else
                Cells(x, 8).FormulaR1C1 = "=Sum(R" & Cells(x - 1, 8).End(xlUp).Row & "C[0]:R[-1]C[0])"
            End If
            Range(Cells(x, 8), Cells(x, 20)).Formula = Cells(x, 8).Formula
        End If
    Next x
    End Sub
    1. Include a sample workbook with an example of the result you want
    2. Use [CODE] and [FORMULA] wrappers for your VBA code or excel formulas
    3. If your question has been answered, mark the thread as SOLVED and click on the "Add Rep" star to thank whoever helped you.

  3. #3
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Summing Blank Rows in a file

    Quote Originally Posted by k64 View Post
    Try this:

    Sub sums()
    For x = 5 To ActiveSheet.UsedRange.Rows.Count
        If Cells(x, 8) = "" And Cells(x - 1, 8) <> "" Then
            If Cells(x - 2, 8) = "" Then
                Cells(x, 8).FormulaR1C1 = "=R[-1]C[0]"
            Else
                Cells(x, 8).FormulaR1C1 = "=Sum(R" & Cells(x - 1, 8).End(xlUp).Row & "C[0]:R[-1]C[0])"
            End If
            Range(Cells(x, 8), Cells(x, 20)).Formula = Cells(x, 8).Formula
        End If
    Next x
    End Sub
    This code sort of works. It definitely finds the blank rows and fills them with a formula, but the formula is wrong in a sense that it sums all previous tables into the current tables sum formula. Also, sums all the blank rows in between the big tables (The highlighted cells "These rows do not get summed').

  4. #4
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Summing Blank Rows in a file

    Hi Bryce

    Try this Code...
    Option Explicit
    Sub Macro2()
       Dim ws           As Worksheet
       Dim LR           As Long
       Dim FirstRow     As Long
       Dim cel          As Range
    
       Set ws = Sheets("Sheet1")
       FirstRow = 4
       Application.ScreenUpdating = False
       With ws
          LR = .Range("H" & .Rows.Count).End(xlUp).Row + 1
    
          For Each cel In .Range("H5:H" & LR).SpecialCells(xlCellTypeBlanks)
             cel.Formula = "=Sum(H" & FirstRow & ":H" & cel.Row - 1 & ")"
             .Range(cel.Address).AutoFill Destination:=.Range(cel.Address & ":T" & cel.Row)
    
             With .Range(cel.Address & ":T" & cel.Row).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
             End With
    
             With .Range(cel.Address & ":T" & cel.Row).Borders(xlEdgeBottom)
                .LineStyle = xlDouble
                .Weight = xlThick
             End With
    
             FirstRow = cel.Row + 1
          Next cel
       End With
       Application.ScreenUpdating = True
    End Sub
    How does one know where these Rows are going to be...any way to know that? Does the Worksheet actually say "These rows do not get summed"?
    the blank rows in between the big tables (The highlighted cells "These rows do not get summed').
    Attached Files Attached Files
    Last edited by jaslake; 03-16-2017 at 03:50 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  5. #5
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Summing Blank Rows in a file

    Quote Originally Posted by jaslake View Post
    How does one know where these Rows are going to be...any way to know that? Does the Worksheet actually say "These rows do not get summed"?
    The actual wb doesn't say "These rows do not get summed", I just edited that in.

    On the gap in between the large tables, there is no absolute row value in which the gap appears. As the year goes on, more rows will be added to both tables, pushing the gap down.

    The script works pretty good though. The only set back was that summed the rows in the gap between the large tables. Also bold the formulas, but that should be a quick fix.

  6. #6
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Summing Blank Rows in a file

    Hi Bryce

    Yes, quick fix...
    Also bold the formulas, but that should be a quick fix.
    You mention "both tables". How is this single Worksheet created from those "both tables"? What's the process...perhaps we can deal with these outliers up front. What's your "RAW Data" look like...or am I looking at it.

  7. #7
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Summing Blank Rows in a file

    Quote Originally Posted by jaslake View Post
    Hi Bryce

    Yes, quick fix...


    You mention "both tables". How is this single Worksheet created from those "both tables"? What's the process...perhaps we can deal with these outliers up front. What's your "RAW Data" look like...or am I looking at it.
    I added this code to bold the sum rows

    With .Range(cel.Address & ":T" & cel.Row).Select
             Selection.Font.Bold = True
             End With
    The report itself is just raw data exported straight from SAP.

    Still not sure how to check if the blank rows need filled in with the formula or not. Possibly a countif function?

    Let me know if you can think of anything.

  8. #8
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Summing Blank Rows in a file

    Hi Bryce...try my latest...let me know.

  9. #9
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Summing Blank Rows in a file

    Hi Bryce

    This Code in the attached appears to deal with these...let me know...
    The only set back was that summed the rows in the gap between the large tables. Also bold the formulas, but that should be a quick fix.
    Option Explicit
    Sub Macro2()
       Dim ws           As Worksheet
       Dim LR           As Long
       Dim FirstRow     As Long
       Dim rng          As Range
       Dim r            As Range
       Dim cel          As Range
    
       Set ws = Sheets("Sheet1")
       FirstRow = 4
       Application.ScreenUpdating = False
       With ws
    
          LR = .Range("A" & .Rows.Count).End(xlUp).Row
          .Range("A5:A" & LR).SpecialCells(xlCellTypeBlanks).Select
    
          Set rng = Selection
          If Not rng Is Nothing Then
             For Each r In rng.Areas   'loop through each area within range
                If r.Rows.Count > 1 Then
                   .Cells(r.Row + 1, "A").Resize(r.Rows.Count - 1, 1).EntireRow.Delete
                End If
             Next r
          End If
    
          LR = .Range("H" & .Rows.Count).End(xlUp).Row + 1
    
          For Each cel In .Range("H5:H" & LR).SpecialCells(xlCellTypeBlanks)
             cel.Formula = "=Sum(H" & FirstRow & ":H" & cel.Row - 1 & ")"
             .Range(cel.Address).AutoFill Destination:=.Range(cel.Address & ":T" & cel.Row)
    
             With .Range(cel.Address & ":T" & cel.Row).Borders(xlEdgeTop)
                .LineStyle = xlContinuous
                .Weight = xlThin
             End With
    
             With .Range(cel.Address & ":T" & cel.Row).Borders(xlEdgeBottom)
                .LineStyle = xlDouble
                .Weight = xlThick
             End With
    
             With .Range(cel.Address & ":T" & cel.Row).Font
                .FontStyle = "Bold"
             End With
    
             FirstRow = cel.Row + 1
          Next cel
       End With
       Application.ScreenUpdating = True
    End Sub
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Summing Blank Rows in a file

    Quote Originally Posted by jaslake View Post
    Hi Bryce

    This Code in the attached appears to deal with these...let me know...

    Interesting way of dealing with the blank rows in between tables, but can't argue with the results. It works perfect. Awesome job!

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Summing Blank Rows in a file

    Hi Bryce

    It's what I had in my toolbox...
    Interesting way of dealing with the blank rows in between tables
    Glad it works for you...thanks for the Rep.

  12. #12
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Summing Blank Rows in a file

    My bad, I didn't think about the fact that previous blank rows wouldn't be blank anymore. Simple fix:

    Sub sums()
    For x = ActiveSheet.UsedRange.Rows.Count to 5
        If Cells(x, 8) = "" And Cells(x - 1, 8) <> "" Then
            If Cells(x - 2, 8) = "" Then
                Cells(x, 8).FormulaR1C1 = "=R[-1]C[0]"
            Else
                Cells(x, 8).FormulaR1C1 = "=Sum(R" & Cells(x - 1, 8).End(xlUp).Row & "C[0]:R[-1]C[0])"
            End If
            Range(Cells(x, 8), Cells(x, 20)).Formula = Cells(x, 8).Formula
        End If
    Next x
    End Sub

  13. #13
    Forum Contributor
    Join Date
    02-20-2017
    Location
    Indiana
    MS-Off Ver
    2016
    Posts
    101

    Re: Summing Blank Rows in a file

    Quote Originally Posted by k64 View Post
    My bad, I didn't think about the fact that previous blank rows wouldn't be blank anymore. Simple fix:

    End Sub[/CODE]
    This code didn't seem to do anything. VBA didn't error out. not sure what happened. I'll take a closer look at it tomorrow when I'm back at work. The file is attached if you want to try running it.

  14. #14
    Valued Forum Contributor
    Join Date
    08-06-2013
    Location
    Detroit, Michigan
    MS-Off Ver
    Excel 2013
    Posts
    671

    Re: Summing Blank Rows in a file

    That's what I get for rushing. Hopefully third time is a charm:

    Sub sums()
    For x = ActiveSheet.UsedRange.Rows.Count to 5 step -1
        If Cells(x, 8) = "" And Cells(x - 1, 8) <> "" Then
            If Cells(x - 2, 8) = "" Then
                Cells(x, 8).FormulaR1C1 = "=R[-1]C[0]"
            Else
                Cells(x, 8).FormulaR1C1 = "=Sum(R" & Cells(x - 1, 8).End(xlUp).Row & "C[0]:R[-1]C[0])"
            End If
            Range(Cells(x, 8), Cells(x, 20)).Formula = Cells(x, 8).Formula
        End If
    Next x
    End Sub

+ 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. Need some help please - Macro to create a CSV file and remove blank rows.
    By stuhelhall in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 02-05-2016, 09:34 PM
  2. Replies: 2
    Last Post: 09-18-2014, 12:20 PM
  3. Replies: 4
    Last Post: 09-16-2014, 10:48 AM
  4. Text file consolidation and deletion of blank rows
    By watiwawa in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2014, 01:20 AM
  5. [SOLVED] Help with Summing Duplicates, deleting rows after summing and filling col. for unsummed
    By solidrock1 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-04-2014, 09:11 AM
  6. Exclude blank rows from CSV file
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 01-05-2011, 10:52 AM
  7. Output CSV file creates commas down blank rows
    By mcinnes01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-05-2010, 05:54 AM

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