How to find the range of cells till which data is present

[Srinatw]

Hi,

Greetings Everyone...
Am very new to using Macros in Excel.

Please help on the below query.

In Sheet1, I have two columns named "Test Case" and "Result"
The Test Case column has many rows and new data will get appended as and when required.

I need a way to find out how many rows of data does the column named "Test Case" contains and then need to mention the range for some other calculation.

To be precise,

I need to filter Result column based on its values Pass or Fail and need to copy the filtered data to new sheet.

I need to copy only the rows which are visible after applying the Pass of Fail filter.
How to find the range of cells which are available after applying the filter.

Thanks much in Advance.
Srinatw

 

[apo]

Hi..

Say, for example.. your autofilter range is on Sheet1 from "A4:M4".. (thats the first row of auto filtered data below the autofilter header..)

This code will set "myrange" to the range address of the visible range after autofilter...

  Dim myrange As Range


  Set myrange = Sheets("Sheet1").Range("A4:M" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
  Debug.Print myrange.Address

 

[Srinatw]

Hi apo,

Thanks a lot.

Hi..

Say, for example.. your autofilter range is on Sheet1 from "A4:M4".. (thats the first row of auto filtered data below the autofilter header..)

This code will set "myrange" to the range address of the visible range after autofilter...

  Dim myrange As Range


  Set myrange = Sheets("Sheet1").Range("A4:M" & Cells(Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
  Debug.Print myrange.Address