Fillright

  • Thread starter Thread starter Stan
  • Start date Start date
S

Stan

I have this small macro that I am using but I can not
figure out how to add to it so it will fill to the right.
I use this macro on various sheets so the amount of
columns and rows change. I would also like to change this
row to Bold font. tia



MyRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]c:R[-
1]C,0)"
 
The count of used columns uses the column property, so your code would be as
follows.....

MyCols = ActiveSheet.UsedRange.Column.Count

Cheers
N


Stan said:
I have this small macro that I am using but I can not
figure out how to add to it so it will fill to the right.
I use this macro on various sheets so the amount of
columns and rows change. I would also like to change this
row to Bold font. tia



MyRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]c:R[-
1]C,0)"
Click to expand...
 
looks like you are putting this formula at the bottom of the column of
numbers - since you want to start with row 1 down to the row above the cell
with the formula
ActiveCell.FormulaR1C1 = "=Countif(R1C:R[-1]C)"
 
my macro works but I don't know how to fillright. In
other words, I get the right answer at the end of
column "E", but now how do I modify or add to this macro
so it will fill across all of my columns to the right?

tia

-----Original Message-----
looks like you are putting this formula at the bottom of the column of
numbers - since you want to start with row 1 down to the row above the cell
with the formula
ActiveCell.FormulaR1C1 = "=Countif(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy

I have this small macro that I am using but I can not
figure out how to add to it so it will fill to the right.
I use this macro on various sheets so the amount of
columns and rows change. I would also like to change this
row to Bold font. tia



MyRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]c:R [-
1]C,0)"
Click to expand...


.
Click to expand...
 
Sub Macro1()
MyRows = ActiveSheet.UsedRange.Rows.Count
mycols = ActiveSheet.UsedRange.Columns.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & _
"]c:R[-1]C,0)"
Set rng = Range(ActiveCell, Cells(ActiveCell.Row, mycols))
ActiveCell.AutoFill Destination:=rng, Type:=xlFillDefault
End Sub


--
Regards,
Tom Ogilvy

Stan said:
my macro works but I don't know how to fillright. In
other words, I get the right answer at the end of
column "E", but now how do I modify or add to this macro
so it will fill across all of my columns to the right?

tia

-----Original Message-----
looks like you are putting this formula at the bottom of the column of
numbers - since you want to start with row 1 down to the row above the cell
with the formula
ActiveCell.FormulaR1C1 = "=Countif(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy

I have this small macro that I am using but I can not
figure out how to add to it so it will fill to the right.
I use this macro on various sheets so the amount of
columns and rows change. I would also like to change this
row to Bold font. tia



MyRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]c:R [-
1]C,0)"
Click to expand...


.
Click to expand...
Click to expand...
 
Awesome Tom, Thanks!!

-----Original Message-----
Sub Macro1()
MyRows = ActiveSheet.UsedRange.Rows.Count
mycols = ActiveSheet.UsedRange.Columns.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & _
"]c:R[-1]C,0)"
Set rng = Range(ActiveCell, Cells(ActiveCell.Row, mycols))
ActiveCell.AutoFill Destination:=rng, Type:=xlFillDefault
End Sub


--
Regards,
Tom Ogilvy

my macro works but I don't know how to fillright. In
other words, I get the right answer at the end of
column "E", but now how do I modify or add to this macro
so it will fill across all of my columns to the right?

tia

-----Original Message-----
looks like you are putting this formula at the bottom
Click to expand...
of
the column of
numbers - since you want to start with row 1 down to
Click to expand...
the
row above the cell
with the formula
ActiveCell.FormulaR1C1 = "=Countif(R1C:R[-1]C)"

--
Regards,
Tom Ogilvy

I have this small macro that I am using but I can not
figure out how to add to it so it will fill to the right.
I use this macro on various sheets so the amount of
columns and rows change. I would also like to change this
row to Bold font. tia



MyRows = ActiveSheet.UsedRange.Rows.Count
ActiveCell.FormulaR1C1 = "=COUNTIF(R[-" & MyRows & "]
Click to expand...
c:R
[-
1]C,0)"


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top