Insert 4 Columns If only 9 Columns
 

Hi

I import text from a web page then run a macro, yet on some occasions there
are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which as
the word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not Correct. -------
4 Columns are missing. Cell L1 which as the word Result in it.

How can I check for this, and if there is only 11 blank cells insert 4 new
columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave

Insert 4 Columns If only 9 Columns
 

this may work for you:

Sub insertColumns()
Dim ws As Worksheet
Dim firstcol As Long
Set ws = Worksheets("Sheet1")
firstcol = ws.Cells(1, 1).End(xlToRight).Column

If firstcol < 16 Then
Columns("A:A").Resize(, 16 - firstcol).Insert
End If

End Sub

--


Gary


"DaveM" wrote in message
...
Hi

I import text from a web page then run a macro, yet on some occasions there
are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which as the
word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not Correct. ------- 4
Columns are missing. Cell L1 which as the word Result in it.

How can I check for this, and if there is only 11 blank cells insert 4 new
columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave

Insert 4 Columns If only 9 Columns
 

Try incorportating or calling this sub within yours

Sub InsertColumnsIf()
On Error GoTo ok
fc = Cells(1, 1).End(xlToRight).Column
'MsgBox fc
Range(Cells(1, 1), Cells(1, 16 - fc)) _
..EntireColumn.Insert 'columns(2)nc=15-fc
ok:
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
...
Hi

I import text from a web page then run a macro, yet on some occasions
there are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which as
the word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not Correct. -------
4 Columns are missing. Cell L1 which as the word Result in it.

How can I check for this, and if there is only 11 blank cells insert 4 new
columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave

Insert 4 Columns If only 9 Columns
 

Hi Gary and Don

Thanks for the replies

Both work OK, Subs insert before column A.
I was hoping to insert the 4 columns before Column L.

Thanks in advance

Dave




"Don Guillett" wrote in message
...
Try incorportating or calling this sub within yours

Sub InsertColumnsIf()
On Error GoTo ok
fc = Cells(1, 1).End(xlToRight).Column
'MsgBox fc
Range(Cells(1, 1), Cells(1, 16 - fc)) _
.EntireColumn.Insert 'columns(2)nc=15-fc
ok:
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
...
Hi

I import text from a web page then run a macro, yet on some occasions
there are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which
as the word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not
Correct. ------- 4 Columns are missing. Cell L1 which as the word
Result in it.

How can I check for this, and if there is only 11 blank cells insert 4
new columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave

Insert 4 Columns If only 9 Columns
 

try this then:

Sub insertColumns()
Dim ws As Worksheet
Dim firstcol As Long
Set ws = Worksheets("Sheet1")
firstcol = ws.Cells(1, 1).End(xlToRight).Column

If firstcol < 16 Then
Columns("L:L").Resize(, 16 - firstcol).Insert
End If

End Sub




"DaveM" wrote in message
...
Hi Gary and Don

Thanks for the replies

Both work OK, Subs insert before column A.
I was hoping to insert the 4 columns before Column L.

Thanks in advance

Dave




"Don Guillett" wrote in message
...
Try incorportating or calling this sub within yours

Sub InsertColumnsIf()
On Error GoTo ok
fc = Cells(1, 1).End(xlToRight).Column
'MsgBox fc
Range(Cells(1, 1), Cells(1, 16 - fc)) _
.EntireColumn.Insert 'columns(2)nc=15-fc
ok:
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
...
Hi

I import text from a web page then run a macro, yet on some occasions
there are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which
as the word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not
Correct. ------- 4 Columns are missing. Cell L1 which as the word
Result in it.

How can I check for this, and if there is only 11 blank cells insert 4
new columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave

Insert 4 Columns If only 9 Columns
 

Works a Treat

Thanks Gary

Thanks Don

All the Best

Dave



"Gary Keramidas" wrote in message
...
try this then:

Sub insertColumns()
Dim ws As Worksheet
Dim firstcol As Long
Set ws = Worksheets("Sheet1")
firstcol = ws.Cells(1, 1).End(xlToRight).Column

If firstcol < 16 Then
Columns("L:L").Resize(, 16 - firstcol).Insert
End If

End Sub




"DaveM" wrote in message
...
Hi Gary and Don

Thanks for the replies

Both work OK, Subs insert before column A.
I was hoping to insert the 4 columns before Column L.

Thanks in advance

Dave




"Don Guillett" wrote in message
...
Try incorportating or calling this sub within yours

Sub InsertColumnsIf()
On Error GoTo ok
fc = Cells(1, 1).End(xlToRight).Column
'MsgBox fc
Range(Cells(1, 1), Cells(1, 16 - fc)) _
.EntireColumn.Insert 'columns(2)nc=15-fc
ok:
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveM" wrote in message
...
Hi

I import text from a web page then run a macro, yet on some occasions
there are 4 columns missing?

Example

Correct
Starting From A1 to O1 (15) cells are blank, This is OK. Cell P1 which
as the word Result in it.

Incorrect
Starting From A1 to (11) K1 cells are blank, This is Not
Correct. ------- 4 Columns are missing. Cell L1 which as the word
Result in it.

How can I check for this, and if there is only 11 blank cells insert 4
new columns before Cell L1 which as the word Result in it.

And If Correct, Do Nothing

Thanks in advance

Dave