rows
 

how can i select a whole row minus the first few columns?

rows
 

I'll go with the general belief that "few" = 3. If this needs changed, change
the constant "FewRow"

Sub SelectMostOfRow()
Const FewRow As Long = 3
Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow).Select
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

how can i select a whole row minus the first few columns?

rows
 

this works but how do i stick this in as a range for formulas

"Charles Chickering" wrote:

I'll go with the general belief that "few" = 3. If this needs changed, change
the constant "FewRow"

Sub SelectMostOfRow()
Const FewRow As Long = 3
Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow).Select
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

how can i select a whole row minus the first few columns?

rows
 

I'm not entirely sure that I know what you're asking but if you want to use
the address of those cells in a formula, try something like this:
Sub MakeFormula()
Const FewRow As Long = 3
Dim r1 As Range
Set r1 = Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow)
'If you want to set a column to the sum of the cells,
'increment with the row, try this:
Range("A1:A2").Formula = "=Sum(" & r1.Address(False, True) & ")"

End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

this works but how do i stick this in as a range for formulas

"Charles Chickering" wrote:

I'll go with the general belief that "few" = 3. If this needs changed, change
the constant "FewRow"

Sub SelectMostOfRow()
Const FewRow As Long = 3
Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow).Select
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

how can i select a whole row minus the first few columns?

rows
 

lets say i want to count all the times 'a' comes up
so i write out =countif (1:1,"a") and i get a message that i created a circle
how do i input the range to be all but the first 3 columns?


"Charles Chickering" wrote:

I'm not entirely sure that I know what you're asking but if you want to use
the address of those cells in a formula, try something like this:
Sub MakeFormula()
Const FewRow As Long = 3
Dim r1 As Range
Set r1 = Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow)
'If you want to set a column to the sum of the cells,
'increment with the row, try this:
Range("A1:A2").Formula = "=Sum(" & r1.Address(False, True) & ")"

End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

this works but how do i stick this in as a range for formulas

"Charles Chickering" wrote:

I'll go with the general belief that "few" = 3. If this needs changed, change
the constant "FewRow"

Sub SelectMostOfRow()
Const FewRow As Long = 3
Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow).Select
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

how can i select a whole row minus the first few columns?

rows
 

Pretty much the same.

Dim r1 As Range
Set r1 = Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow)
'If you want to set a column to the sum of the cells,
'increment with the row, try this:
Range("A1:A2").Formula = "=CountIf(" & r1.Address(False, True) & ",""a"")"

End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

lets say i want to count all the times 'a' comes up
so i write out =countif (1:1,"a") and i get a message that i created a circle
how do i input the range to be all but the first 3 columns?


"Charles Chickering" wrote:

I'm not entirely sure that I know what you're asking but if you want to use
the address of those cells in a formula, try something like this:
Sub MakeFormula()
Const FewRow As Long = 3
Dim r1 As Range
Set r1 = Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow)
'If you want to set a column to the sum of the cells,
'increment with the row, try this:
Range("A1:A2").Formula = "=Sum(" & r1.Address(False, True) & ")"

End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

this works but how do i stick this in as a range for formulas

"Charles Chickering" wrote:

I'll go with the general belief that "few" = 3. If this needs changed, change
the constant "FewRow"

Sub SelectMostOfRow()
Const FewRow As Long = 3
Range("A1").EntireRow.Resize(, _
Columns.Count - FewRow).Offset(, FewRow).Select
End Sub
--
Charles Chickering

"A good example is twice the value of good advice."


"lifeguardernie" wrote:

how can i select a whole row minus the first few columns?