Last_column and autofill ...help!
 

Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down) and
autofill to last row AND THEN one more row
** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7
** copy/paste_values the inserted formulas

Can you please help me build this code?

Thanks!
Ray

Last_column and autofill ...help!
 

wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down) and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL values in
Column 7. Then, the formula (above) copied down (in the far-right column)
will use these totals. Is that fairly clear? Sorry for confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down) and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

Ok, try this

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 =
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ray" wrote in message
...
Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL values in
Column 7. Then, the formula (above) copied down (in the far-right
column)
will use these totals. Is that fairly clear? Sorry for confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down)
and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

Hi Bob -

I entered the code as written below and when I try to run it, I get:
Run-time error '1004':
Application-defined or object-defined error

Ideas?
thanks, ray


Bob Phillips wrote:
Ok, try this

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 =
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ray" wrote in message
...
Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL values in
Column 7. Then, the formula (above) copied down (in the far-right
column)
will use these totals. Is that fairly clear? Sorry for confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down)
and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

As follow-up, I checked the worksheet and most of the code seems to
have worked - the two columns are deleted and the correct formula has
been entered and copied down to the last existing cell.

The formula was not copied down one add'l row, nor were the other
columns totaled down.

rgds, ray


wrote:
Hi Bob -

I entered the code as written below and when I try to run it, I get:
Run-time error '1004':
Application-defined or object-defined error

Ideas?
thanks, ray


Bob Phillips wrote:
Ok, try this

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 =
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ray" wrote in message
...
Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL values in
Column 7. Then, the formula (above) copied down (in the far-right
column)
will use these totals. Is that fairly clear? Sorry for confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row down)
and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

I mis-interpreted that bit

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 2).FormulaR1C1 = _
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol - 3).Resize(, 2).FormulaR1C1 = _
"=SUM(R4C:R[-1]C)"
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
As follow-up, I checked the worksheet and most of the code seems to
have worked - the two columns are deleted and the correct formula has
been entered and copied down to the last existing cell.

The formula was not copied down one add'l row, nor were the other
columns totaled down.

rgds, ray


wrote:
Hi Bob -

I entered the code as written below and when I try to run it, I get:
Run-time error '1004':
Application-defined or object-defined error

Ideas?
thanks, ray


Bob Phillips wrote:
Ok, try this

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 =
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ray" wrote in message
...
Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL
values in
Column 7. Then, the formula (above) copied down (in the far-right
column)
will use these totals. Is that fairly clear? Sorry for
confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all
together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row
down)
and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas

Last_column and autofill ...help!
 

Perfect! Thanks very much for your help....
//ray


Bob Phillips wrote:
I mis-interpreted that bit

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 2).FormulaR1C1 = _
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol - 3).Resize(, 2).FormulaR1C1 = _
"=SUM(R4C:R[-1]C)"
End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

wrote in message
ups.com...
As follow-up, I checked the worksheet and most of the code seems to
have worked - the two columns are deleted and the correct formula has
been entered and copied down to the last existing cell.

The formula was not copied down one add'l row, nor were the other
columns totaled down.

rgds, ray


wrote:
Hi Bob -

I entered the code as written below and when I try to run it, I get:
Run-time error '1004':
Application-defined or object-defined error

Ideas?
thanks, ray


Bob Phillips wrote:
Ok, try this

Sub LastColAutofill()
Dim iLastRow As Long
Dim iLastCol As Long

iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
If iLastCol 2 Then
Columns(iLastCol).Offset(0, -2).Resize(, 2).Delete
ElseIf iLastCol 1 Then
Columns(iLastCol).Offset(0, -2).Delete
End If
iLastCol = Cells(1, Columns.Count).End(xlToLeft).Column
iLastRow = Cells(Rows.Count, iLastCol).End(xlUp).Row
Cells(4, iLastCol + 1).Resize(iLastRow - 3).FormulaR1C1 =
"=RC[-3]/RC[-4]"
Cells(iLastRow + 1, iLastCol + 1).FormulaR1C1 = "=SUM(R4C:R[-1]C)"
End Sub


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Ray" wrote in message
...
Hi Bob -

Formula will be: "=RC[-3]/RC[-4]"

And I need a Total of ALL values in Column 6 and a total of ALL
values in
Column 7. Then, the formula (above) copied down (in the far-right
column)
will use these totals. Is that fairly clear? Sorry for
confusion...

//ray


"Bob Phillips" wrote:


wrote in message
ups.com...
Hello -

I've been searching this forum for help and while I've found
bits/pieces of useful code, I'm not sure how to put it all
together.
Here's what I need to do: [Excel2002, windows XP]
** find last column with data
** delete two columns, one column from the end
- Ex if sheet has 10columns, delete #8 & 9
** in the first blank column, insert fomula (starting 4th row
down)
and
autofill to last row AND THEN one more row


What formula?


** then total the values in two columns
- Ex if sheet has 10col, total #6 & 7


Why 6 & 7, why not 11?


** copy/paste_values the inserted formulas