Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

Excel VBA, insert a row below if formula is true

Posted on 2014-09-26
Medium Priority
1,403 Views
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
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
• 2
• 2

LVL 49

Expert Comment

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
``````
0

LVL 27

Accepted Solution

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
``````

Regards,
-Glenn
0

LVL 49

Expert Comment

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

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))
``````

-Glenn
0

Featured Post

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses
Course of the Month7 days, 14 hours left to enroll