insert a new row in VB

[associates]

Hi,

I was wondering if anyone might be able to help me here.

How do i insert a new row in a worksheet in excel using VB programming?

For example, in a worksheet called Data, the following is the data

A B C
1 Merchandise 500 600
2 02-000 200 200
3 02-001 300 600
4 Total 1000 1400
5 Grocery 200 80
6 03-001 200 200
7 ...

I'd like to be able to insert a new row after "Total" which is after line no. 4, just to separate them between merchandise and grocery (formatting issue).

I've got some codes but not working really yet (got some errors)

If insertnewline = True Then
Worksheets("Data").Range("A4").Select
Selection.Insert Shift:=xlDown
end if

The error message that i got is
run-time error '1004'
select method of range class failed

I don't know why it doesn't like the range.

Your help is greatly appreciated.

Thank you in advance

[Ken Johnson]

Hi,
you will get that error if the worksheet is protected.
If this is the case then your code has to unprotect the sheet at the
start, then reapply protection at the end.

Ken Johnson

[Ken Johnson]

Hi again,

Also, your code only inserts a cell in column A. If you want columns B
and C to be similarly affected then try...

If insertnewline = True Then
With Worksheets("Data")
.Unprotect (password) 'if protected without password leave the
brackets and contents
.Range("A4:C4").insert Shift:= xlDown
.Protect
End With
End if

or if you want to insert an entire row across the sheet then try...

If insertnewline = True Then
With Worksheets("Data")
.Unprotect (password) 'if protected without password leave the
brackets and contents
.Range("A4").EntireRow.Insert
.Protect
End With
End if

Ken Johnson

[Ken Johnson]

Hi again,again

I'd better clear up my mistake with the password stuff.

If the sheet is protected without a password then your code only needs
to say .Unprotect then .Protect

If a password such as abc was used then the code should be .Unprotect
(abc) then
..Protect (abc)

Ken Johnson

[Don Guillett]

something like

for i= cells(rows.count,"a").end(xlup).row to 2 step -1
if ucase(cells(i,"a"))= "TOTAL" then rows(i).insert
next i

--
Don Guillett
SalesAid Software
[email protected]
"associates" <[email protected]> wrote
in message news:[email protected]...
>
> Hi,
>
> I was wondering if anyone might be able to help me here.
>
> How do i insert a new row in a worksheet in excel using VB programming?
>
>
> For example, in a worksheet called Data, the following is the data
>
> A B C
> 1 Merchandise 500 600
> 2 02-000 200 200
> 3 02-001 300 600
> 4 Total 1000 1400
> 5 Grocery 200 80
> 6 03-001 200 200
> 7 ...
>
> I'd like to be able to insert a new row after "Total" which is after
> line no. 4, just to separate them between merchandise and grocery
> (formatting issue).
>
> I've got some codes but not working really yet (got some errors)
>
> If insertnewline = True Then
> Worksheets("Data").Range("A4").Select
> Selection.Insert Shift:=xlDown
> end if
>
> The error message that i got is
> run-time error '1004'
> select method of range class failed
>
> I don't know why it doesn't like the range.
>
> Your help is greatly appreciated.
>
> Thank you in advance
>
>
> --
> associates
> ------------------------------------------------------------------------
> associates's Profile:
> http://www.excelforum.com/member.php...o&userid=35073
> View this thread: http://www.excelforum.com/showthread...hreadid=548249
>