Solved

Excel VBA, insert a row below if formula is true

Posted on 2014-09-26
4
1,263 Views
Last Modified: 2014-09-26
I am looking for a way using VBA in Excel to insert a blank row below a cell if my formula is true.  So, i have column H with data.  I want to insert a blank row under any cell that ends with  the text Total.
0
Comment
Question by:jnikodym
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40346454
Sub InsertRows()

Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("H1048576").End(xlUp).Row

For lngRow = lngLastRow To 1 Step -1
    If Right$(Cells(lngRow, 8).Text, 5) = "Total" Then
        Cells(lngRow, 8).Offset(1, 0).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If
Next
End Sub

Open in new window

0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40346459
If your data is continuous (no blank cells in column H, then this code will work:
Sub Insert_Row_After_Total()
    Set Rng = Range("H1", Range("H1").End(xlDown))
    For Each cl In Rng
        If InStr(1, cl.Value, "total", vbTextCompare) > 0 Then
            cl.Offset(1, 0).EntireRow.Insert
        End If
    Next cl
End Sub

Open in new window


Regards,
-Glenn
0
 
LVL 47

Expert Comment

by:Martin Liss
ID: 40346468
Ignore my code. It only adds a blank cell rather than a blank row. You could change my line 10 to

Cells(lngRow, 8).Offset(1, 0).EntireRow.Insert

but Glenn already gave essentially that solution.
0
 
LVL 27

Expert Comment

by:Glenn Ray
ID: 40346486
Appending my previous post:  If your data is NOT continuous (actually, "contiguous") - meaning that there may be blank cells in your range of data in column H - then replace the second line of my previous code with this
set rng = Range("H1", Range("H" & Cells.Rows.Count).End(xlUp))

Open in new window


-Glenn
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question