Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Hello,
I want to write a program that will search down a column to find the first empty cell, insert a row at that point, and then perform sums for certain columns. Also, I want to create line formats for each column. The first empty cell occurrence will be variable, but the columns will be fixed....Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Assuming the column will never be blank or have less than 2 rows, and
headers are in row1. Sub AddFormulas() Cells(1,1).End(xldown).offset(1,0).Range( _ "A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)" End Sub adds formulas in column A, C, F through J. Adjust to suit. -- Regards, Tom Ogilvy "Matt" wrote in message ... Hello, I want to write a program that will search down a column to find the first empty cell, insert a row at that point, and then perform sums for certain columns. Also, I want to create line formats for each column. The first empty cell occurrence will be variable, but the columns will be fixed....Thanks! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Thanks much Tom....does the End(xldown) bring me to the last cell of the
column with anything in it? And which piece of the code inserts a new row? Thanks!! "Tom Ogilvy" wrote: Assuming the column will never be blank or have less than 2 rows, and headers are in row1. Sub AddFormulas() Cells(1,1).End(xldown).offset(1,0).Range( _ "A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)" End Sub adds formulas in column A, C, F through J. Adjust to suit. -- Regards, Tom Ogilvy "Matt" wrote in message ... Hello, I want to write a program that will search down a column to find the first empty cell, insert a row at that point, and then perform sums for certain columns. Also, I want to create line formats for each column. The first empty cell occurrence will be variable, but the columns will be fixed....Thanks! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Added Tom's code for inserting formuals into this, might not look
pretty and prob abaly smarter ways of doing it but... Sub AddFormulas() Range("A1").Select Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row + 2, 1) = "" If ActiveCell = "" Then Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Else Cells(ActiveCell.Row + 1, 1).Select End If Loop ActiveCell = ActiveCell.Offset(1, 0) Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Just change the starting cell ie Range("A1").select to suit and change the formuals in ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" to suit. Regards Stopher |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
I am trying this and getting a 400 error...not really sure what that means?
"Tom Ogilvy" wrote: Assuming the column will never be blank or have less than 2 rows, and headers are in row1. Sub AddFormulas() Cells(1,1).End(xldown).offset(1,0).Range( _ "A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)" End Sub adds formulas in column A, C, F through J. Adjust to suit. -- Regards, Tom Ogilvy "Matt" wrote in message ... Hello, I want to write a program that will search down a column to find the first empty cell, insert a row at that point, and then perform sums for certain columns. Also, I want to create line formats for each column. The first empty cell occurrence will be variable, but the columns will be fixed....Thanks! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Stopher wrote: Added Tom's code for inserting formuals into this, might not look pretty and prob abaly smarter ways of doing it but... Sub AddFormulas() Range("A1").Select Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row + 2, 1) = "" If ActiveCell = "" Then Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Else Cells(ActiveCell.Row + 1, 1).Select End If Loop ActiveCell = ActiveCell.Offset(1, 0) Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Just change the starting cell ie Range("A1").select to suit and change the formuals in ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" to suit. Regards Stopher If you wanted it just for the first contiguous block of data then: Sub AddFormulas2() Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Cells(Selection.Item(Selection.Count), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Once again change you starting cell Range("A1").Select to suit Stopher |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Stopher wrote: Stopher wrote: Added Tom's code for inserting formuals into this, might not look pretty and prob abaly smarter ways of doing it but... Sub AddFormulas() Range("A1").Select Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row + 2, 1) = "" If ActiveCell = "" Then Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Else Cells(ActiveCell.Row + 1, 1).Select End If Loop ActiveCell = ActiveCell.Offset(1, 0) Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Just change the starting cell ie Range("A1").select to suit and change the formuals in ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" to suit. Regards Stopher If you wanted it just for the first contiguous block of data then: Sub AddFormulas2() Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Cells(Selection.Item(Selection.Count), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Once again change you starting cell Range("A1").Select to suit Stopher Hmmm worked the first time I tried it, seems to want to go to row 38764...... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Sub AddFormulas2()
Range("A1").Select Range(Selection, Selection.End(xlDown)).Select MsgBox Selection.Count Cells((Selection.Count + 1), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Try this |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Stopher wrote: Sub AddFormulas2() Range("A1").Select Range(Selection, Selection.End(xlDown)).Select MsgBox Selection.Count Cells((Selection.Count + 1), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub Try this Having a great time of it.....take out the msgbox line as well. Was using it to debug. On the 400 error, I'm not sure. Maybe it doesn't like dates, I tried with just numbers in the A column, will have a look at that now. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Ok found an error with the first code I posted, it will chop the last
data line off and insert the formulas, sorry for the multiple posts. Try this... Sub AddFormulas() Range("A1").Select Do Until Cells(ActiveCell.Row + 1, 1) = "" And Cells(ActiveCell.Row + 2, 1) = "" If ActiveCell = "" Then Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Else Cells(ActiveCell.Row + 1, 1).Select End If Loop Cells(ActiveCell.Row + 1, 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
On the 400 error, all I can find is that if you have pasted the code
into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Thank you very much for your help on this Stopher!
"Stopher" wrote: On the 400 error, all I can find is that if you have pasted the code into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
One other question -
Say I want to hide rows after I've done all of the things we have gotten so far with the code...and I want it to start after the sums and everything (very next row) and go to a specific row....I h ave this so far, but am not sure about the syntax cause it isn't working....thanks: Selection.EntireRow.Select Rows(Selection, 1001).Select Selection.EntireRow.Hidden = True So I want it to go down to row 1001 - do I just add 1000 to selection? i.e. Rows(Selection, Selection + 1000)? "Matt" wrote: Thank you very much for your help on this Stopher! "Stopher" wrote: On the 400 error, all I can find is that if you have pasted the code into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Matt wrote: One other question - Say I want to hide rows after I've done all of the things we have gotten so far with the code...and I want it to start after the sums and everything (very next row) and go to a specific row....I h ave this so far, but am not sure about the syntax cause it isn't working....thanks: Selection.EntireRow.Select Rows(Selection, 1001).Select Selection.EntireRow.Hidden = True So I want it to go down to row 1001 - do I just add 1000 to selection? i.e. Rows(Selection, Selection + 1000)? "Matt" wrote: Thank you very much for your help on this Stopher! "Stopher" wrote: On the 400 error, all I can find is that if you have pasted the code into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher So you want to do the formual add on the top block of contiguous data, and then hide everything else underneath?? |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Stopher wrote: Matt wrote: One other question - Say I want to hide rows after I've done all of the things we have gotten so far with the code...and I want it to start after the sums and everything (very next row) and go to a specific row....I h ave this so far, but am not sure about the syntax cause it isn't working....thanks: Selection.EntireRow.Select Rows(Selection, 1001).Select Selection.EntireRow.Hidden = True So I want it to go down to row 1001 - do I just add 1000 to selection? i.e. Rows(Selection, Selection + 1000)? "Matt" wrote: Thank you very much for your help on this Stopher! "Stopher" wrote: On the 400 error, all I can find is that if you have pasted the code into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher So you want to do the formual add on the top block of contiguous data, and then hide everything else underneath?? Don't know which code your applying it to but... Sub AddFormulas2() Dim RowCount As Integer Range("A1").Select Range(Selection, Selection.End(xlDown)).Select RowCount = Selection.Count + 2 Cells((Selection.Count + 1), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Range("A" & RowCount, "G" & RowCount + 1002).Select Selection.EntireRow.Hidden = True End Sub Just added a variable to hold the row number and the hid A - G if this isn't enough then just change the G, not exactly flexible coding but it will do. Stopher |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Make that "G" & RowCount +1002 "G" & RowCount +1000 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Stopher wrote: Stopher wrote: Matt wrote: One other question - Say I want to hide rows after I've done all of the things we have gotten so far with the code...and I want it to start after the sums and everything (very next row) and go to a specific row....I h ave this so far, but am not sure about the syntax cause it isn't working....thanks: Selection.EntireRow.Select Rows(Selection, 1001).Select Selection.EntireRow.Hidden = True So I want it to go down to row 1001 - do I just add 1000 to selection? i.e. Rows(Selection, Selection + 1000)? "Matt" wrote: Thank you very much for your help on this Stopher! "Stopher" wrote: On the 400 error, all I can find is that if you have pasted the code into a sheets secific area, you should paste it in a module. So either on the VBA page insertmodule and paste in there, or on the sheet make it into a windowed view and right click on the title bar and select view code and paste there. Hope this solves the problem. Stopher So you want to do the formual add on the top block of contiguous data, and then hide everything else underneath?? Don't know which code your applying it to but... Sub AddFormulas2() Dim RowCount As Integer Range("A1").Select Range(Selection, Selection.End(xlDown)).Select RowCount = Selection.Count + 2 Cells((Selection.Count + 1), 1).Select Selection.EntireRow.Insert ActiveCell.Range( _ "C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" ActiveCell.Offset(2, 0).Select Range("A" & RowCount, "G" & RowCount + 1002).Select Selection.EntireRow.Hidden = True End Sub Just added a variable to hold the row number and the hid A - G if this isn't enough then just change the G, not exactly flexible coding but it will do. Stopher Well there you go just change the "G" to an "A" anyways because it doesn't only hide the selection but the entire row of every row selected. Stopher |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Just read your comment again and was probaly a bit mislead.
Instead of the Range("A" & RowCount, "A" & Rowcount +1000).select If you want ti to go to specifically row 100 and not the next 1000 rows after the first empty cell after the formula line then: Range("A" & RowCount, "A1000").Select Stopher |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding last cell and insert line
Sub AddFormulas()
Dim rng as Range Set rng = Cells(1, 1).End(xlDown).Offset(1, 0) rng.Range( _ "A1,C1,F1:J1").FormulaR1C1 = "=Sum(R2C:R[-1]C)" rng.EntireRow.Insert End Sub the original worked fine for me. This one inserts a line above the formulas. It worked fine for me as well. -- Regards, Tom Ogilvy "Matt" wrote in message ... I am trying this and getting a 400 error...not really sure what that means? "Tom Ogilvy" wrote: Assuming the column will never be blank or have less than 2 rows, and headers are in row1. Sub AddFormulas() Cells(1,1).End(xldown).offset(1,0).Range( _ "A1,C1,F1:J1").formulaR1C1 = "=Sum(R2C:R[-1]C)" End Sub adds formulas in column A, C, F through J. Adjust to suit. -- Regards, Tom Ogilvy "Matt" wrote in message ... Hello, I want to write a program that will search down a column to find the first empty cell, insert a row at that point, and then perform sums for certain columns. Also, I want to create line formats for each column. The first empty cell occurrence will be variable, but the columns will be fixed....Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I insert a new line of text inside a cell | New Users to Excel | |||
How can I insert a line feed in the text in an EXCEL cell? | Excel Discussion (Misc queries) | |||
Insert horizontal line/single cell in Excel? | Excel Discussion (Misc queries) | |||
Possible to insert cell data in the middle of line of text? | Excel Worksheet Functions | |||
Sum previous cell? Insert line.... | Excel Programming |