Inserting multiple columns without a loop

FNeeq

New Member
Joined
Oct 2, 2014
Messages
15
I am trying to insert about 10 columns before a column with header "Analysis". Below is the code I am currently using. I will like to know if I can do this without a loop. Thanks

Code:
    Dim fnRange As Range

    Set fnRange = Worksheets(ActiveSheet.Name).Range(Range("A1"), Range("A1").End(xlToRight)).Find(what:="Analysis", _
    LookAt:=xlWhole, MatchCase:=False)
    
    With fnRange.Select
    
        For lngCount = 1 To 10
            Selection.EntireColumn.Insert
        Next lngCount
        
    End With
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
This should do the trick or so i think
Code:
Columns(fnRange.Column).Resize(, 10).Insert

so the code now looks like this
Code:
Dim fnRange As Range
Set fnRange = Worksheets(ActiveSheet.Name).Range(Range("A1"), Range("A1").End(xlToRight)).Find(what:="Analysis", _
    LookAt:=xlWhole, MatchCase:=False)
Columns(fnRange.Column).Resize(, 10).Insert
 

Momentman

Well-known Member
Joined
Jan 11, 2012
Messages
4,040
Office Version
  1. 365
Platform
  1. Windows
So take it that it worked, right? :)

You are welcome
 

Watch MrExcel Video

Forum statistics

Threads
1,109,351
Messages
5,528,192
Members
409,807
Latest member
nicky736

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top