Insert Blank Lines Based on Cell

[JPSailes]

Currently using .Range (G4) to determine number of blank rows. However, what I need is for the range to be entire column G:G (that are not null). A better way?

With ws
    Set NBOFROWS = .Range(G4)
    ActiveCell.EntireRow.Offset(1).Resize(NBOFROWS.Value).Insert Shift:=xlDown
  End With

Ultimately, I'm copying down Row based on G. Using the below. So probably an easier route than this.

Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.FormulaR1C1 = "=R[-1]C"

 

[louisH]

Hi,

What do you have in G4 ?

To get count of blank values in a range :

nbEmpty = Columns("G:G").SpecialCells(xlCellTypeBlanks).Count

It is never a good solution to use Select. You can just do :

Columns("A:A").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = ...

 

[JPSailes]

Thanks. G4, or all the G:G contains the number of cells I need to insert blanks below (and ultimately fill down).

So if G4 is 5, I need to insert 5 blank rows below Row 4.

In theory, if I could use the current cell selection, I could make this work using relative references. But it seems it would be ugly.

I don't have permission to attach example.