Solved

Excel VBA, insert a row below if formula is true

Posted on 2014-09-26
4
1,199 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to know if my Checkbox is True in VB6.0? 9 40
Highlighting cells in Excel 9 17
InternetExplorer object in Excel VBA. 4 21
Most Consistent Performer 4 20
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

910 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now