Inserting number of rows based on a count of data

**Ranelagh** · 08-15-2012, 07:47 AM

Hi,

I have a list of data in column A that changes length. I'm looking for a macro that can insert columns from column O based on twice the count of data in column A.

eg. if there are 5 entries in Column A, I'd like to insert 10 columns from O. I have the macro to insert columns but it needs an exact number.

Columns("O:O").Select
    Range(Selection, Selection.Columns(No of columns)).EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

Any help would be great.

Thanks.

**patel45** · 08-15-2012, 09:20 AM

Sub Insert_Columns()
    Dim num As String
    num = InputBox("How many columns do you want to insert?")
    If num <> "" Then
        If num > 0 Then ActiveCell.Offset(0, 1).Resize(1, num).EntireColumn.Insert
    End If

End Sub

**Ranelagh** · 08-15-2012, 10:23 AM

Hi patel,

That's a great code and will be useful. But for the current file, I'm looking for it to count the number of cells with data and insert twice the number of columns. I'm looking to have as few manual inputs as possible.

Thanks.

**MarvinP** · 08-15-2012, 10:30 AM

Hi Ranelagh,

It looks like you need some code to count the number of rows in column A.

I use the following to do that

Dim LastRowA as Double
LastRowA = Cells(Rows.Count,"A").End(xlUp).Row

Then you might be able to do this in your code.

Range(Selection, Selection.Columns(LastRowA * 2)).EntireColumn.Select

Hope that helps.

Look at http://answers.microsoft.com/en-us/o...c-f38bb66afcaa or search for Rows.Count and LastRow on the net for more examples.

Inserting number of rows based on a count of data

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Inserting number of rows based on a count of data

Re: Inserting number of rows based on a count of data

Re: Inserting number of rows based on a count of data

Re: Inserting number of rows based on a count of data

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions