Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make Excel add an empty row every filled one in a list .
Hi!
I have got a list of dates in a Excel spreadsheet with day and month in the first column 'A' and the corresponding week day in the second column 'B'. I obtained this list just by writing the date and week day in the first row and then drop down the list I needed. If I want to add row/s between the dates (so each row), since now, I have been selecting each row manually, then right clicked and selected 'add row'. Yet, if I have to do this hundreds of time it becomes very annoying and time consuming. Anybody knows if there is any formula that can be used to add one or more rows every filled one? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make Excel add an empty row every filled one in a list .
Formulas cannot insert rows.
VBA can insert rows. Sub InsertRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long numRows = 1 For r = 1000 To 1 Step -1 'adjust the 1000 to a more meaningful range ActiveSheet.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 5 Mar 2008 14:56:02 -0800, Diego C. <Diego wrote: Hi! I have got a list of dates in a Excel spreadsheet with day and month in the first column 'A' and the corresponding week day in the second column 'B'. I obtained this list just by writing the date and week day in the first row and then drop down the list I needed. If I want to add row/s between the dates (so each row), since now, I have been selecting each row manually, then right clicked and selected 'add row'. Yet, if I have to do this hundreds of time it becomes very annoying and time consuming. Anybody knows if there is any formula that can be used to add one or more rows every filled one? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make Excel add an empty row every filled one in a li
Excellent it works!!
Thank you very much indeed Gord Dibben! I did not know that something such as VBA existed before today! Just to add what I did from someone that understand nothing about it. So that if someone like me needs help, this is probably going to save him/her time. I selected the Worksheet I was working on, I went in Tool, Micro, Visual Basic Editor Select Insert, Module, Once the windows opens copy and paste - just - the formula below and press play on the top row of the Visual Basic for Application Windows. Thanks! "Gord Dibben" wrote: Formulas cannot insert rows. VBA can insert rows. Sub InsertRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long numRows = 1 For r = 1000 To 1 Step -1 'adjust the 1000 to a more meaningful range ActiveSheet.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 5 Mar 2008 14:56:02 -0800, Diego C. <Diego wrote: Hi! I have got a list of dates in a Excel spreadsheet with day and month in the first column 'A' and the corresponding week day in the second column 'B'. I obtained this list just by writing the date and week day in the first row and then drop down the list I needed. If I want to add row/s between the dates (so each row), since now, I have been selecting each row manually, then right clicked and selected 'add row'. Yet, if I have to do this hundreds of time it becomes very annoying and time consuming. Anybody knows if there is any formula that can be used to add one or more rows every filled one? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How can I make Excel add an empty row every filled one in a li
I usually add instructions for adding macros to a workbook and running them.
Apologies for my rushing and not including the instructions. Glad to hear you got it running on your own. Gord On Thu, 6 Mar 2008 02:57:00 -0800, Diego C. wrote: Excellent it works!! Thank you very much indeed Gord Dibben! I did not know that something such as VBA existed before today! Just to add what I did from someone that understand nothing about it. So that if someone like me needs help, this is probably going to save him/her time. I selected the Worksheet I was working on, I went in Tool, Micro, Visual Basic Editor Select Insert, Module, Once the windows opens copy and paste - just - the formula below and press play on the top row of the Visual Basic for Application Windows. Thanks! "Gord Dibben" wrote: Formulas cannot insert rows. VBA can insert rows. Sub InsertRows() Application.ScreenUpdating = False Dim numRows As Integer Dim r As Long numRows = 1 For r = 1000 To 1 Step -1 'adjust the 1000 to a more meaningful range ActiveSheet.Rows(r + 1).Resize(numRows).EntireRow.Insert Next r Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Wed, 5 Mar 2008 14:56:02 -0800, Diego C. <Diego wrote: Hi! I have got a list of dates in a Excel spreadsheet with day and month in the first column 'A' and the corresponding week day in the second column 'B'. I obtained this list just by writing the date and week day in the first row and then drop down the list I needed. If I want to add row/s between the dates (so each row), since now, I have been selecting each row manually, then right clicked and selected 'add row'. Yet, if I have to do this hundreds of time it becomes very annoying and time consuming. Anybody knows if there is any formula that can be used to add one or more rows every filled one? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Count filled data rows until empty | Excel Discussion (Misc queries) | |||
Macro Request: Fill in empty cells with previous Filled cell in column | Excel Worksheet Functions | |||
How can Excel make a cell empty without making it na()? | Excel Discussion (Misc queries) | |||
Copy Data From Filled to Empty Cells | Excel Discussion (Misc queries) | |||
How to make empty cells as zero in excel add-ins for SQL Server an | Excel Worksheet Functions |