Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify ADD ROWS Code - Excel 2003
Do Until Irow = 4
to whatever the lastrow you want to process is. Say row 2 Do Until Irow = 2 -- Regards, Tom Ogilvy " 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify ADD ROWS Code - Excel 2003
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify ADD ROWS Code - Excel 2003
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Modify ADD ROWS Code - Excel 2003
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 " 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Modify Excel (2003) code for Network environment | Setting up and Configuration of Excel | |||
Modify Macro Code Depending on Excel Version | Excel Discussion (Misc queries) | |||
User not being prompted to enter Password to modify Excel 2003 | Excel Discussion (Misc queries) | |||
modify xlsx files with excel 2003 | Excel Discussion (Misc queries) | |||
How can I surpass/modify the Excel limit of 65k rows? | Excel Discussion (Misc queries) |