+ Reply to Thread
Results 1 to 2 of 2

insert cell and shift cells right at every blank cell in column

  1. #1
    Registered User
    Join Date
    05-29-2012
    Location
    Birmingham, AL
    MS-Off Ver
    Office 2016
    Posts
    8

    insert cell and shift cells right at every blank cell in column

    My data is in blocks ranging from A to AB, rows vary. Each block of data is separated by an inserted row which now contains some formulas and subtotals. Only the subtotals in AB matter to me.

    At every empty cell in column G, I need to insert a cell and shift cells right. This will push the subtotals in AB out into their own column, AC.

    I'm very new to macros and to this forum. I realize this is very simple for most. Sorry I have to ask.

    Here is what I have written. This does what I want, except that it shifts cells down for the insert. I need to shift cells right.

    Sub ShiftRightAtG()
    Range("G8:G35268").SpecialCells(xlCellTypeBlanks).Select
    Selection.Insert
    Range("A1").Select 'Get back to top of range
    End Sub
    Last edited by zmarsh; 05-29-2012 at 02:18 PM.

  2. #2
    Registered User
    Join Date
    05-29-2012
    Location
    Birmingham, AL
    MS-Off Ver
    Office 2016
    Posts
    8

    Re: insert cell and shift cells right at every blank cell in column

    I had a friend help me out. The fix is below.

    One spreadsheet was really long, but not wide. Even so, I kept getting an error that I was trying to push non-blank cells off the sheet to the right. So I had to adjust the range (highlighted) to only do 10,000 rows at a time. That worked.

    Sub ShiftRightAtG()
    Range("G8:G10000").SpecialCells(xlCellTypeBlanks).Select
    Selection.Insert Shift:=xlToRight
    Range("A1").Select 'Get back to top of range
    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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1