Solved

Excel VBA, insert a row below if formula is true

Posted on 2014-09-26
4
1,229 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
  • 2
  • 2
4 Comments
 
LVL 46

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 46

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

777 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