PDA

View Full Version : Solved: Insert columns by using a criteria



Shazam
02-15-2006, 05:51 AM
Hey everyone!


May I have this code be modified to do a criteria? Meaning this code below insert 3 columns can the code start inserting 3 columns starting with the first date in row 2 because the my data changes daily. I put a sample of my workbook below. Please look at the worksheet tab named "results". It would give you a better explanation.

Sub InsertColumns()
For colx = 30 To 5 Step -1
Columns(colx).Insert Shift:=xlToLeft
Columns(colx).Insert Shift:=xlToLeft
Columns(colx).Insert Shift:=xlToLeft
Next
End Sub

mvidas
02-15-2006, 06:33 AM
Hey Shazam,Sub InsertColumns2()
Dim FCol As Long, LCol As Long, ColX As Long
FCol = 1
Do Until IsDate(Cells(2, FCol))
FCol = FCol + 1
Loop
LCol = Cells(2, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For ColX = LCol To FCol Step -1
Range(Columns(ColX), Columns(ColX + 2)).EntireColumn.Insert
Next
Application.ScreenUpdating = True
End SubMatt

Shazam
02-15-2006, 08:06 AM
Thanks mvidas it works very well. One more thing where in the code that I could change instead looking for the first date it starts inserting columns after the second date. Sample workbook below.

mvidas
02-15-2006, 08:27 AM
Sure thing, I had to add a boolean variable to the mix to make sure the first was found first:Sub InsertColumns3()
Dim FCol As Long, LCol As Long, ColX As Long, FDateFound As Boolean
FCol = 1
Do Until IsDate(Cells(2, FCol)) And FDateFound
If IsDate(Cells(2, FCol)) Then FDateFound = True
FCol = FCol + 1
Loop
LCol = Cells(2, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
For ColX = LCol To FCol + 1 Step -1
Range(Columns(ColX), Columns(ColX + 2)).EntireColumn.Insert
Next
Application.ScreenUpdating = True
End SubMatt

Shazam
02-15-2006, 08:40 AM
Thank You very much!:bow: