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!

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!

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!

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

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!

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

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......

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

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.

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

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

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

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

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??

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

Finding last cell and insert line
 

Make that "G" & RowCount +1002

"G" & RowCount +1000

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

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

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!