Modify ADD ROWS Code - Excel 2003

Hello,

I found this code when researching to add rows to a worksheet. Could
someone help me modify it so that when I'm running the code to add rows
to multiple worksheets in a workbook that does not have but 3 rows
bucause this code will not work if the worksheet does not have at least
4 rows of text?

I have 75 worksheets in my workbook but some of the worksheets have
only 3 rows of text and the below code will stop working. How can the
code be changed so that if there is no text in the 4 row the code will
know to move to the next worksheet in the workbook?

Sub Add_rows()
'will put 3 row between rows
Irow = Range("A65536").End(xlUp).Row
Application.ScreenUpdating = False
Do Until Irow = 4
Set Rng = Range("A" & Irow)
Rng.Resize(3, 1).EntireRow.Insert
Irow = Irow - 1
Loop
Application.ScreenUpdating = True
End Sub

Thank you for your help in advance,
jfcby

Do Until Irow = 4

to whatever the lastrow you want to process is. Say row 2

Do Until Irow = 2

--
Regards,
Tom Ogilvy


"[email protected]" wrote:

> Hello,
>
> I found this code when researching to add rows to a worksheet. Could
> someone help me modify it so that when I'm running the code to add rows
> to multiple worksheets in a workbook that does not have but 3 rows
> bucause this code will not work if the worksheet does not have at least
> 4 rows of text?
>
> I have 75 worksheets in my workbook but some of the worksheets have
> only 3 rows of text and the below code will stop working. How can the
> code be changed so that if there is no text in the 4 row the code will
> know to move to the next worksheet in the workbook?
>
> Sub Add_rows()
> 'will put 3 row between rows
> Irow = Range("A65536").End(xlUp).Row
> Application.ScreenUpdating = False
> Do Until Irow = 4
> Set Rng = Range("A" & Irow)
> Rng.Resize(3, 1).EntireRow.Insert
> Irow = Irow - 1
> Loop
> Application.ScreenUpdating = True
> End Sub
>
> Thank you for your help in advance,
> jfcby
>
>

Hello Tom,

Thanks for the fast reply! But I did not describe my problem right. If
the worksheet only has 4 rows the code will stop working. Can the code
be modified so that it will move to the next sheet in the workbook?

Thank you for your help,
jfcby

Hello,

I figured out my problem! When I ran another code it moved data to the
end of some of my worksheets and when I got a error message saying to
use control end to find data at the of the worksheet it had some how
moved my data to the last row in my worksheets. So I got a fresh copy
of my workheets tried the new code and it works fine!

Thank you for your help,
jfcby

Sub Add_rows()
Dim sh as Worksheet, Irow as Long
Dim rng as Range
Application.ScreenUpdating = False
'will put 3 row between rows
for each sh in activeworkbook.Worksheets
sh.Activate
Irow = Range("A65536").End(xlUp).Row

Do Until Irow = 4
Set Rng = Range("A" & Irow)
Rng.Resize(3, 1).EntireRow.Insert
Irow = Irow - 1
Loop
Next sh
Application.ScreenUpdating = True
End Sub

--
Regards,
Tom Ogilvy

"[email protected]" wrote:

> Hello,
>
> I found this code when researching to add rows to a worksheet. Could
> someone help me modify it so that when I'm running the code to add rows
> to multiple worksheets in a workbook that does not have but 3 rows
> bucause this code will not work if the worksheet does not have at least
> 4 rows of text?
>
> I have 75 worksheets in my workbook but some of the worksheets have
> only 3 rows of text and the below code will stop working. How can the
> code be changed so that if there is no text in the 4 row the code will
> know to move to the next worksheet in the workbook?
>
> Sub Add_rows()
> 'will put 3 row between rows
> Irow = Range("A65536").End(xlUp).Row
> Application.ScreenUpdating = False
> Do Until Irow = 4
> Set Rng = Range("A" & Irow)
> Rng.Resize(3, 1).EntireRow.Insert
> Irow = Irow - 1
> Loop
> Application.ScreenUpdating = True
> End Sub
>
> Thank you for your help in advance,
> jfcby
>
>