Insert columns

[sag_wi]

Is it possible to insert columns between exisiting columns all at one time?

EX. If I have text in columns 1,2,3,4,5,6,7,8,9,10, and would like to have a blank column between each of those columns, can it be done all at one time, instead of each one individually?

Thanks,
Sag

 

[Ged]

Yes.
If it's columns it will be ABCDEFG etc.
Click on column B, then ctrl+column C, then ctrl+column D etc and you'll get blank columns.
You must use the ctrl key to add extra columns. If you use the shift key, say column B, shift+column C, you'll get two blank columns as it sees columns B & C as the two columns to be moved as a single item.

 

[sag_wi]

insert columns

Actually it's rows that I'm looking to insert, not columns. Can I insert rows between rows all at one time? Its it similar the the insert column you have below?

Thanks,
sag

 

[erik.van.geit]

Hi, sag,

just control-click all rows where you want to insert a row
do you have a lot of rows to insert, then some tricks can be applied

kind regards,
Erik

 

[sag_wi]

insert columns

I have data on all row from say 1 to 200. I need to have a blank row between each of those 200 rows. So yes, i have kind a few rows to insert.

I'm unsure what you mean, when you say control-click? Hold the control key and "cick"what?

 

[erik.van.geit]

Re: insert columns

I'm unsure what you mean, when you say control-click? Hold the control key and "cick"what?

control+click on the rowheaders (the labels "1","2","3" before the rows)

some code

Option Explicit

Sub make_selection_to_add_blank_rows()
'Erik Van Geit
'060630

Dim tmp As Range
Dim rng As Range
Dim FR As Long          'First Row
Dim LR As Long          'Last Row
Dim CR As Long          'Count Rows
Dim NR As Integer       '# rows to insert

    On Error Resume Next
    Set tmp = Application.InputBox(prompt:="Select the range where you want to insert rows", _
    Title:="SELECTION", Default:=Selection.Address, Type:=8)
    On Error GoTo 0
    If tmp Is Nothing Then Exit Sub

FR = tmp(1).Row
CR = tmp.Rows.Count
LR = FR + CR - 1

    NR = Application.InputBox("Please enter the number of rows to insert", "# ROWS", Type:=1)
    If NR = False Then
    MsgBox "No rows will be inserted", 48, "Operation aborted"
    Exit Sub
    End If

Application.ScreenUpdating = False

Columns(1).Insert
Set rng = Range(Cells(FR, 1), Cells(LR, 1))

    With rng
    Cells(FR, 1) = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1
    Rows(LR + 1 & ":" & LR + NR * CR).Insert Shift:=xlDown
    .Copy .Offset(CR, 0).Resize(CR * NR, 1)
    .Resize(CR * (NR + 1)).EntireRow.Sort Key1:=Cells(FR, 1), Order1:=xlAscending, Header:=xlNo
    .EntireColumn.Delete
    End With

Application.ScreenUpdating = True

End Sub

if you will always insert only one row then replace

    NR = Application.InputBox("Please enter the number of rows to insert", "# ROWS", Type:=1)
    If NR = False Then
    MsgBox "No rows will be inserted", 48, "Operation aborted"
    Exit Sub
    End If

by

    NR = 1

the range where rows must be inserted, can be found programatically: then you need to define the criteria (like: first row = 5, last row = last used row in column A or last used row anywhere on sheet)

best regards,
Erik

 

[sag_wi]

Erik,

Thank you for the code, it did the trick. Took me awhile to figure out how to add the macro (new girl here). But it worked, once I got it in.
Is there some way to add the macro out, so I can use it with any new work book I open, or do I have to add it out everytime, I want to use it?

Thanks so much!!
Sag.

 

[erik.van.geit]

you can run the code with any workbook active

a professional would make it an add-in or put it in his personal.xls
see documentation on personal.xls (it's your personal workbook)

honnestly - some could laugh at me - I never used my personal.xls, though playing with Excel for at least thousand hours a year
I prefer to take the macro from my library, put it in a workbook and run it
to run macros: menu Extra/Macro/Macro's or hit Alt+F8 pick from list and "run" or double-click

what do you think ?

 

[sag_wi]

I have added it to my personal.xls, so I can run with any workbook. Do you know if its true that storing macro's in your personal.xls slows down your machine?
I have no clue what any of macro language means.......it's all gibberish to me. I hope to someday learn how to write them....this one is going to save me, much time.
Thanks again for all of your help.

 

[erik.van.geit]

I have added it to my personal.xls, so I can run with any workbook. Do you know if its true that storing macro's in your personal.xls slows down your machine?
I have no clue what any of macro language means.......it's all gibberish to me. I hope to someday learn how to write them....this one is going to save me, much time.
Thanks again for all of your help.

be happy, you've done a thing I never did

to learn about macros
start recording some very simple things
see how it comes out in your VBEditor
try to EDIT a bit
browse the Board or buy some book
AND see the HELPFILES !

enjoy Excelling
Erik