Help with insert a row

[Guest]

I am looking for something that will search col. inputed in an input box,
then check for any of the values that I have in an array, when found, insert
a blank row above it. Here is the code I am using. I am getting an error
message regarding the method can not be used with the range? can some one
help please.
**********************************************
Sub Insert_Row_with_Array()

'This will inseret a row for the selected cell which contains any word
in an array.
'enter only the column letter (without the digit)
'in the input box.

'Keyboard short cut: Ctrl+i

Dim rng As Range, objActiveWkb As Object, objActiveWksht As Object
Dim calcmode As Long
Dim myArr As Variant
Dim I As Long
Dim Message, Title, Default, MyValue

Message = "Enter column letter only" ' Set prompt.
Title = "Delete rows of designated cells" ' Set title.
'Default = "1" ' Set default.
' Display message, title, and default value.

MyValue = InputBox(Message, Title, Default)

'If rng Is Nothing Then
' Exit Sub
' Else

With Application
calcmode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Fill in the values that you want to delete
myArr = Array("Building", "Building Number", "GSF", "CRV($000's)")

For I = LBound(myArr) To UBound(myArr)

'Sheet with the data, you can also use Sheets("MySheet")
With ActiveSheet

'Firstly, remove the AutoFilter
.AutoFilterMode = False

'Apply the filter
.Range(MyValue & "1:" & MyValue & .Rows.Count).AutoFilter
Field:=1, Criteria1:=myArr(I)

Set rng = Nothing
With .AutoFilter.Range
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0
If Not rng Is Nothing Then rng.EntireRow.Insert
End With

'Remove the AutoFilter
.AutoFilterMode = False
End With

Next I

With Application
.ScreenUpdating = True
.Calculation = calcmode
End With
'End If
'ActiveWorkbook.Save
End Sub
**********************************
the statment that is causing the error is:
"rng.EntireRow.Insert"
thanks
Al

 

[Bill Renaud]

What is the Array in your code?
Array("Building", "Building Number", "GSF", "CRV($000's)")

Is this the list of column labels at the top of your list of data, or
actual values in your list?

If these are column labels (row 1), then the AutoFilter statement inside
your With statement is only filtering a single column, which does not
make sense.
..Range(MyValue & "1:" & MyValue & .Rows.Count).AutoFilter Field:=1,
Criteria1:=myArr(I)

I would suggest adding some variables to set all of your ranges to, then
step through your code and check the locals window to see that they are
set properly. This should help you find the problem. MyValue was set to
a single column (the "MyValue = InputBox(Message, Title, Default)"
statement earlier).

Remember, the easiest way to set a range for an entire list of data
(assuming that it is all that is on the worksheet) is:
Dim rngMyList as Range

Set rngMyList = ActiveSheet.UsedRange

rngMyList.AutoFilter Field:=1, Criteria1:= ....

 

[Guest]

These are column labels that I can use any of them to specify the column. I
really do not need all of them any one will do. These values are repeated
every few rows. Here is a sample of the data in the spread sheet:
********************************************************
Building Primate Clinic CRV($000's) $603 Building Number 504 GSF 2,455

Subsystem Backlog 2008 2009 2010 2011 2012
j.1. Fire Detection Systems $8 $0 $0 $0 $0 $0
l.1. Interior Finishes $0 $0 $0 $0 $0 $32
************************************************************
Here is what the data means:
Building = column lable, Primate Clinic = value, CRV($000's) = column lable,
$603 is the value, Building Number = column lable, 504 = value, GSF = column
lable, 2,455 = value.

Subsystem, Backlog, 2008, 2009, 2010, 2011, 2012 = column lables
the data below them are their values. this pattern repeats in the spread
sheet. not every building will have 2 subsystems, some may have more rows.

Here is what I am trying to accomplish:

I am only interested in "Building" and "Building Number" as lables to be
moved next to the Subsystem, Backlog, 2008, 2009, 2010, 2011, 2012 lables and
then their values moved next to the values of the other lables so that it
should look like this:
*********************************************************
Building Building Number Subsystem Backlog 2008 2009 2010 2011 2012
Primate Clinic 504 j.1. Fire... $8 $0 $0 $0 $0
$0
l.1. Inte... $0 $0 $0
$0 $0 $32
*********************************************************
the data above is what I want to accomplish eventually, if you have a better
way, I am open. I hope that this is clear and I hope that the format stays
the way I typed it. If you want I can email you the file in excel.
I really appreciate your help. It will help me a lot to get this issue
resolved.
thanks again
Al

 

[Guest]

The lay out got messed up here it is again:
*********************************************************
Building Building Number Subsystem Backlog 2008 2009 2010 2011 2012
Primate Clinic 504 j.1. Fire... $8 $0 $0 $0 $0
$0
Primate Clinic 504 l.1. Inte... $0 $0 $0 $0 $0
$32
*********************************************************
Hope this one works
Al