Insert columns until blank column count = 10

[will.smith3]

Hi Everyone, here is my initial post to the forum! I have been working in Excel VBA for the past few months and have watched the whole tutorial from the great folks over at WiseOwl on Excel VBA (https://www.youtube.com/playlist?list=PLNIs-AWhQzckr8Dgmgb3akx_gFMnpxTN5), but I cannot seem to figure this problem out.

I want to keep adding columns after D1 "Week Created", shifting to the right, until I get to 10. But I can't figure out how to set the range properly in the WorksheetFunction below.

'inserts blank columns to prep for the insertion into the new worksheet
    'Columns("H:O").Insert Shift:=xlToRight

    '<< FIX ME >>
    'insert IF statement to count the number of cells between Short Description (G) and Priority
    'This way we will not keep adding columns; 10 columns are needed.
    
    BlankColumnCount = Application.WorksheetFunction.CountBlank(Range("C:J"))
    Do Until BlankColumnCount = 10
        Columns("H").Insert Shift:=xlToRight
    Loop

 

[Nebu]

Hi:

I am afraid I fully understood your requirement is the number 10 going to be constant , Is that you want 10 blank columns added after range D1 ?

Thanks

 

[will.smith3]

Yes. I want 10 columns after D.

 

[Nebu]

Hi:

If it is going to be static why can't you add just a command for inserting 10 columns after column D1
something like

[E1].Resize(, 10).EntireColumn.Insert

Thanks

 

[will.smith3]

It sounds silly, but I hadn't even considered that! Thanks Nebu.