Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Excel VBA, insert a row below if formula is true

Posted on 2014-09-26
4
Medium Priority
?
1,471 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 50

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 2000 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 50

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

564 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