Hi Guys
I wanna be able to filter my set of data and then copy the autofilte
data to a new sheet and only apply formatting to those rows tha
contain data. My function copies the data correctly but i dont know ho
to pass the "RangeOfFilteredData" value into the subroutine, AND whic
function to use to select a row range when i know the column range i
always 8, but the row range is always differing, eg the variabl
"RangeOfFilteredData"


Code
-------------------

sub blah()
Sheets("List").Select 'Contains the data
Selection.AutoFilter Field:=9, Criteria1:="<N/A" ' filtered data in sheet
Set NewOustandingRC = ActiveSheet.AutoFilter.Range ' copy fitered data
NewOustandingRC.Copy

RangeOfFilteredData = ActiveSheet.AutoFilter.Range
Sheets("Outstanding Issues").Select 'Paste data here
Range("A2").Select
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Formatting(RangeOfFilteredData) ' <-Run Formatting Function

'UnFilter List in first sheet
Sheets("List").Select ' LIST
Selection.AutoFilter Field:=9
End Sub

Sub Formatting(Range As Integer)
Range("A2:I??????").Select <- I know the range
Selection.Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

-------------------

--
tarn
-----------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...fo&userid=3229
View this thread: http://www.excelforum.com/showthread.php?threadid=52043

Try replace
Range("A2:I??????")

with
Range("A2").currentregion

"tarns" wrote:


Hi Guys
I wanna be able to filter my set of data and then copy the autofilter
data to a new sheet and only apply formatting to those rows that
contain data. My function copies the data correctly but i dont know how
to pass the "RangeOfFilteredData" value into the subroutine, AND which
function to use to select a row range when i know the column range is
always 8, but the row range is always differing, eg the variable
"RangeOfFilteredData"


Code:
--------------------

sub blah()
Sheets("List").Select 'Contains the data
Selection.AutoFilter Field:=9, Criteria1:="<N/A" ' filtered data in sheet
Set NewOustandingRC = ActiveSheet.AutoFilter.Range ' copy fitered data
NewOustandingRC.Copy

RangeOfFilteredData = ActiveSheet.AutoFilter.Range
Sheets("Outstanding Issues").Select 'Paste data here
Range("A2").Select
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Formatting(RangeOfFilteredData) ' <-Run Formatting Function

'UnFilter List in first sheet
Sheets("List").Select ' LIST
Selection.AutoFilter Field:=9
End Sub

Sub Formatting(Range As Integer)
Range("A2:I??????").Select <- I know the range
Selection.Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression, Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

--------------------


--
tarns
------------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
View this thread: http://www.excelforum.com/showthread...hreadid=520437

Yep, that solved everything, Been looking for about 3 weeks for that
answer and never knew currentregion existed.

thanks

KC Wrote:
Try replace
Range("A2:I??????")

with
Range("A2").currentregion

"tarns" wrote:


Hi Guys
I wanna be able to filter my set of data and then copy the
autofilter
data to a new sheet and only apply formatting to those rows that
contain data. My function copies the data correctly but i dont know
how
to pass the "RangeOfFilteredData" value into the subroutine, AND
which
function to use to select a row range when i know the column range
is
always 8, but the row range is always differing, eg the variable
"RangeOfFilteredData"


Code:
--------------------

sub blah()
Sheets("List").Select 'Contains the data
Selection.AutoFilter Field:=9, Criteria1:="<N/A" ' filtered data
in sheet
Set NewOustandingRC = ActiveSheet.AutoFilter.Range ' copy fitered
data
NewOustandingRC.Copy

RangeOfFilteredData = ActiveSheet.AutoFilter.Range
Sheets("Outstanding Issues").Select 'Paste data here
Range("A2").Select
Range("A2").PasteSpecial Paste:=xlPasteValuesAndNumberFormats

Formatting(RangeOfFilteredData) ' <-Run Formatting Function

'UnFilter List in first sheet
Sheets("List").Select ' LIST
Selection.AutoFilter Field:=9
End Sub

Sub Formatting(Range As Integer)
Range("A2:I??????").Select <- I know the range
Selection.Interior.ColorIndex = 37
Selection.FormatConditions.Add Type:=xlExpression,
Formula1:="=MOD(ROW(),2)=1"
Selection.FormatConditions(1).Interior.ColorIndex = 36
End Sub

--------------------


--
tarns

------------------------------------------------------------------------
tarns's Profile:
http://www.excelforum.com/member.php...o&userid=32291
View this thread:
http://www.excelforum.com/showthread...hreadid=520437




--
tarns
------------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
View this thread: http://www.excelforum.com/showthread...hreadid=520437

How would to insert a formula to sum up all data in the current range if
it matches a value without hardcoding a large number like the 1000th row
of column C ??


Code:
--------------------

Sheets("List").Select
ListRowCount = ActiveSheet.UsedRange.Rows.Count 'Unfiltered Data.

Sheets("Complete Issues").Activate
Range("D7").Formula = "=COUNTIF(List!C3:C1000,C7)"


' I Wish to use something like to select a dynamic row range based on a variable .... "=COUNTIF(List!(Range("C3")(C,ListRowCount)),C 7)" ????

OR something like....

StartRow = 3
EndRow = Cells(ActiveSheet.UsedRange.Rows.Count, "C").End(xlUp).Row

But i need help cos it dont work.

--------------------


--
tarns
------------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
View this thread: http://www.excelforum.com/showthread...hreadid=520437

I think your endrow is
range("C65536").end(xlup).row

"tarns" wrote:


How would to insert a formula to sum up all data in the current range if
it matches a value without hardcoding a large number like the 1000th row
of column C ??


Code:
--------------------

Sheets("List").Select
ListRowCount = ActiveSheet.UsedRange.Rows.Count 'Unfiltered Data.

Sheets("Complete Issues").Activate
Range("D7").Formula = "=COUNTIF(List!C3:C1000,C7)"


' I Wish to use something like to select a dynamic row range based on a variable .... "=COUNTIF(List!(Range("C3")(C,ListRowCount)),C 7)" ????

OR something like....

StartRow = 3
EndRow = Cells(ActiveSheet.UsedRange.Rows.Count, "C").End(xlUp).Row

But i need help cos it dont work.

--------------------


--
tarns
------------------------------------------------------------------------
tarns's Profile: http://www.excelforum.com/member.php...o&userid=32291
View this thread: http://www.excelforum.com/showthread...hreadid=520437