+ Reply to Thread
Results 1 to 6 of 6

Insert a blank row in middle of a range

  1. #1
    Registered User
    Join Date
    12-05-2006
    Location
    Northern suburbs of Phila PA
    Posts
    78

    Insert a blank row in middle of a range

    As I scan down a multi-column, multi-row range, when a certain condition occurs I want to insert a blank row at that point and push the rest of the range down one row. This is equivalent to the Insert Row command, but must work only within the range, not on the entire worksheet. This push-down may happen many times as I scan down the entire range.

    So far I have not found a combination of the Insert method or Row or Rows properties, or some other combo of methods and properties that will do this. Is there a relatively simple way to do it, or do I have to individually copy each subsequent row down one row position (via a loop, of course). That's certainly doable, but it seems rather awkward and time consuming when there are hundreds of rows.

    Thanks for any words of wisdom on this topic.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Response

    Yes it can be done.

    If you use a line like

    Range(Cells(2, 1), Cells(2, 5)).Insert shift:=xlDown

    (assuming 5 columns) you will get what you need.

    When doing this sort of thing, I've always found it more simple to work from the bottom upwards using a FOR-NEXT loop with a STEP -1. This way you don't have any problems with changing row numbers.

    You might also consider turning off the screen updating while this is running to avoid flicker and make the code execute faster.

    Application.ScreenUpdating = False
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Registered User
    Join Date
    12-05-2006
    Location
    Northern suburbs of Phila PA
    Posts
    78

    Doesn't insert a blan row

    mrice - thanks for response.

    Inserting the code you suggested (adapted for my app) ran but didn't do anything. The range should split when the IF condition is met and show a blank row at that location. But it doesn't happen. In fact it hung up with a 1004 run time error after 19 iterations through the loop. Here's my code; perhaps you can spot my mistake:

    Note: CSVdata originated from a csv file, but was converted to Excel and copied into the same worksheet as other data. Both ranges in the code are on the same tab.

    Sub CSV_RD_Sync()
    'Created: 01/13/07
    'Purpose: Re-align the csv file to account for missing MTU readings.
    ' CSV may not have a reading from every MTU in the RD file's Master
    ' MTU ID list. Find the missing MTU IDs in the CSV, and in each case,
    ' push the remaining CSV list down so that the MTU IDs in both lists
    ' line up perfectly.
    'Trigger:

    Dim RDdata As Range 'all columns in RD file
    Dim CSVdata As Range 'all columns from CSV file
    Dim RDcntr As Integer 'row cntr for RD Data
    Dim CScntr As Integer 'row cntr for CSV data

    Set RDdata = Range("RD_Data")
    Set CSVdata = Range("csv_Data")
    RDcntr = 1
    CScntr = 1

    Do Until RDdata.Cells(RDcntr, 5) = "" 'loop through entire RD MTU ID list
    If RDdata.Cells(RDcntr, 5) <> CSVdata(CScntr, 3) Then 'MTU IDs don't match
    CSVdata(Cells(CScntr, 1), Cells(CScntr, 8)).Insert Shift:=xlDown
    CSVdata.Select 'debug use only
    End If
    RDcntr = RDcntr + 1
    CScntr = CScntr + 1
    Loop
    End Sub

  4. #4
    Forum Contributor
    Join Date
    11-29-2003
    Posts
    1,203
    I agree with Martin:
    I've always found it more simple to work from the bottom upwards using a FOR-NEXT loop with a STEP -1.
    If you are NOT going to do that, then be sure to increment your row counter by an additional 1 each time you insert a row.

  5. #5
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,964

    Response

    I suspect that the problem here is that you have defined a range into which you have started to insert extra cells. The fact that it errorred after 19 iterations (a rather arbitary number) would seem to support this.

    You could try removing the

    RDdata.

    from the

    RDdata.Cells(RDcntr, 5)

    line as this may be causing the problem.

  6. #6
    Registered User
    Join Date
    12-05-2006
    Location
    Northern suburbs of Phila PA
    Posts
    78

    Inserting blank rows

    Gentlemen: Thanks for your responses. However, in the meantime I coded the whole thing to work as a For - Next loop, starting at the bottom. And it works like a charm. It properly inserts a blank row anywhere the matching criteria isn't met and pushes everything below it down accordingly, including cases where several consecutive rows don't match. Also works for the very top row and the very bottom.

    So I will try your latest advice sometime as a more or less academic exercise, to learn that much more about VBA. Thanks for your help.

    Ron

    For your edification, here is the final code:

    Sub CSV_RD_Sync()
    'Created: 01/13/07
    'Purpose: Re-align the CSV data to account for missing MTU readings.
    ' CSV may not have a reading from every MTU in the RD file's Master
    ' MTU ID list. Find the missing MTU IDs in the CSV, and in each case,
    ' push the remaining CSV list down so that the MTU IDs in both lists
    ' line up perfectly. This action puts gaps (blanks) in the CSV data.
    'Comment: The data ranges are intended to be longer than the actual
    ' row depth, to accommodate various buildings with more or fewer units.
    'Trigger: Synchronize with RD MAster MTU IDs cmd button

    Dim RDdata As Range 'all columns in RD file
    Dim CSVdata As Range 'all columns from CSV file

    Dim RowCntr As Integer
    Dim Depth As Integer 'depth of filled range (rows)
    Dim xCntr As Integer 'pushdown counter
    Dim NoRead As Integer 'number of rows in CSV with no MTU reading

    Set RDdata = Range("RD_Data")
    Set CSVdata = Range("csv_Data")
    RowCntr = 1

    Do Until RDdata.Cells(Depth, 5) = "" 'find # of contiguous, non-blank rows
    Depth = Depth + 1
    Loop

    If Range("NoSync") = 111 Then 'synch-already-done flag is set
    MsgBox "Already synched; multiple syncs not allowed"
    Exit Sub 'don't allow another synch - results are unpredictable
    End If

    Do Until RDdata.Cells(RowCntr, 5) = "" 'loop through entire RD MTU ID list
    If RDdata(RowCntr, 5) <> CSVdata(RowCntr, 2) Then 'MTU IDs don't match
    'work from bottom up; push stack down by copying each row to next row down
    For xCntr = Depth To RowCntr Step -1 'block of rows to push down
    CSVdata.Rows(xCntr).Copy CSVdata.Rows(xCntr + 1) 'copy to next row below
    Next xCntr
    CSVdata.Rows(RowCntr).ClearContents 'create blank row where MTU reading isn't
    NoRead = NoRead + 1 'record no reading event
    Range("NoSync").Value = 111 'flag to prevent duplicate syncs
    End If
    RowCntr = RowCntr + 1
    Loop
    MsgBox NoRead & " CSV MTUs had no reading"

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts