Use Rows Count to determine number of new rows to insert

[julhs]

I am failing miserably to make use of the Rows Count function!!
Everything I have tried to date has ended up with one error or another!!!
At the moment I have 2 codes, one that gets me what I want to achieve, in a roundabout way, other is floored!!
But, BOTH ultimately require a Row Count
What I have so far is:-
1st code works better for me AS IT STANDS because it DOES NOT rely on a Row Count, but filter values are ALSO pasted into "AM:AP", which then require clearing!!
What I THINK I need is a Row Count of the “UsedRngT”, then ONLY insert/shift down the values into Range("AK" & FrowAr + 2 & ":AL" & FrowAr + 2)
but then use the Row Count result to insert THAT given number of blank rows into Range("AM" & FrowAr + 2 & ":AP" & FrowAr +2)?

Set UsedRngT = Range("AK" & Frow & ":AL" & Lrow) 'UsedRngT is the ACTUAL USED range of "TEMP" range,
Set rngA = Range("AK:AK").Find(what:="Ar Sub Total", LookIn:=xlValues, LookAt:=xlWhole)
FrowAr = rngA.Row
Set rngCopy = UsedRngT
rngCopy.Copy
Range("AK" & FrowAr + 2 & ":AP" & FrowAr + 2).Insert Shift:=xlShiftDown ' BUT filter values are ALSO pasted to "AM:AP"
Set rngAA = Range("AK:AK").Find(what:="Ar Total", LookIn:=xlValues, LookAt:=xlWhole)
LrowAr = rngAA.Row
Range("AM" & FrowAr + 2 & ":AP" & LrowAr - 1).Clear ' This just clears the unwanted filter values from "AM:AP" range

2nd code works to a fashion, BUT it is floored!!
Because, "Range("AK" & FrowAr + 2 & ":AL" & FrowAr + 2) " is a dynamic range, therefore I don’t know how many rows to ACTUALLY insert into “Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2).”

Set UsedRngT = Range("AK" & Frow & ":AL" & Lrow) 'UsedRngT is the ACTUAL used range of "TEMP" range,
Set rngA = Range("AK:AK").Find(what:="Ar Sub Total", LookIn:=xlValues, LookAt:=xlWhole)
FrowAr = rngA.Row
Set rngAA = Range("AK:AK").Find(what:="Ar Total", LookIn:=xlValues, LookAt:=xlWhole)
LrowAr = rngAA.Row
Set rngCopy = UsedRng
rngCopy.Copy
Range("AK" & FrowAr + 2 & ":AL" & FrowAr + 2).Insert Shift:=xlShiftDown
         ' This is adding 3 rows to Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2), but it could require +/- untold number of rows
Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2).Insert Shift:=xlShiftDown
Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2).Insert Shift:=xlShiftDown
Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2).Insert Shift:=xlShiftDown

 

[James006]

Hi,

You mention Rows Count and Filtered Rows ... which is not fully coherent with the portion of macro you have posted ...

It would be Much better to post your Entire macro ...

 

[julhs]

Sorry for delay in replying.
Ignore my previous mention of "Filtered values/rows"
I have made progress with the Row Count.
So I have established the row count of Range("AK" & FrowAr + 2 & ":AK" & LrowAr - 1)
and placed the "nrows value" in "Range("AQ" & FrwD - 2)". I then retrieve that value for use in the last line (Resize / Insert Shift:=xlShiftDown).

The below all works as intended BUT I was wondering if it’s possible to pass the "nrows" value DIRECTLY to the Resize / Insert Shift:=xlShiftDown.?
But everything I’ve tried so far ends up with compile errors.

Option Explicit
Sub RowCount_Insert()
  Dim UsedRngT As Range
  Dim Frow As Long
  Dim Lrow As Long
  Dim rngA As Range
  Dim FrowAr As Long
  Dim LrowAr As Long
  Dim FrwD As Long
  Dim nrows As Integer

  Set UsedRngT = Range("AK" & Frow & ":AL" & Lrow)  'UsedRngT is the ACTUAL used range of "TEMP" range
  Set rngA = Range("AK:AK").Find(what:="Ar Sub Total", LookIn:=xlValues, LookAt:=xlWhole)
       FrowAr = rngA.Row
  Set rngCopy = UsedRngT
       rngCopy.Copy
        Range("AK" & FrowAr + 2 & ":AL" & FrowAr + 2).Insert Shift:=xlShiftDown
      nrows = WorksheetFunction.Count(Range("AK" & FrowAr + 2 & ":AK" & LrowAr - 1))
        'Show the result
      MsgBox "The number of cells populated with values is " & nrows
        Range("AQ" & FrwD - 2) = nrows
        Range("AM" & FrowAr + 2 & ":AP" & FrowAr + 2).Resize(Range("AQ" & FrwD - 2).value).Insert Shift:=xlShiftDown

>>>….. Other code
End Sub