To find the first and last row of a filtered range

[zoepal]

Hi,

Im having problems with the following activity.
I want to filter a column and then fill another column (only with header) with ("Yes") all the way down.

For doing that, I would like to count how many rows are in the visible column, to estimate the last visible row.

Once I got that, I would like that my code will work:

LastRow=Range ("D" & Rows.Count).End(xlUp).Row -1 (headers)

Range("E2:E & LastRow").SpecialCells(xlCellsTypeVisible).Value = "Yes"

Could you please help me with that?

Thanks in advance

[sintek]

What are the two Columns and what are the filter criteria...See Top Yellow banner regarding sample file upload and explain in detail what you want to achieve...

This will give you an idea though...No need for lastrow...

Please Login or Register  to view this content.

[kvsrinivasamurthy]

This will count visible rows

Please Login or Register  to view this content.

[zoepal]

Hi again,

Thanks for your explanation,

I attach a file of what I exactly need.
I have to filter the customer column by the customer e, f and g. Then I need a macro to writ Yes in all of these fields. For the filter I used an array as a criteria to put all the names in one go.

Thanks again,

[sintek]

Please Login or Register  to view this content.

[kvsrinivasamurthy]

Macro code

Please Login or Register  to view this content.

[zoepal]

Hi Sintek,

I wanted to thank for your help, I was able to continue with macros.
However I thought it already worked smoothley until I encounter a minor problem.
I need to sort a column(17) that contains numbers, from 30 to +121, and filter between intervals of numbers. Then, I have to write a value in the other column.

For example, if I sort between >=31 and <=60 and then I have to write in another column the letter E. Then, I have to unsort, put the filters again and find the next interval of numbers >=61 and <=90 and write a letter D. I have to repeat that process until I got all these values.

The problem that I encounter is that the macro ignore the first intervals and I only get the column18 filled by 91-120 even if the column 17 have smaller numbers like 89, 40, 30.... It seems to work only when i filter with just all numbers greater than 121. So I assume Im doing something wrong when setting the filter between two numbers.



With Cells(1).CurrentRegion

.AutoFilter 5, ""
.AutoFilter 17, ">=31"
.AutoFilter 17, "<=60"
.Offset(1).Resize(.Rows.Count - 1).Columns(18).Value = "31-60"
.Offset(1).Resize(.Rows.Count - 1).Columns(19).Value = "E"
.AutoFilter


.AutoFilter 5, ""
.AutoFilter 17, ">=61"
.AutoFilter 17, "<=90"
.Offset(1).Resize(.Rows.Count - 1).Columns(18).Value = "61-90"
.Offset(1).Resize(.Rows.Count - 1).Columns(19).Value = "D"
.AutoFilter


.AutoFilter 5, ""
.AutoFilter 17, ">=91"
.AutoFilter 17, "<=120"
.Offset(1).Resize(.Rows.Count - 1).Columns(18).Value = "91-120"
.Offset(1).Resize(.Rows.Count - 1).Columns(19).Value = "C"
.AutoFilter


.AutoFilter 5, ""
.AutoFilter 17, ">=121"
.Offset(1).Resize(.Rows.Count - 1).Columns(18).Value = "121+"
.Offset(1).Resize(.Rows.Count - 1).Columns(19).Value = "B"
.AutoFilter
End With


I appreciate a lot your help,
Kind regards

[zoepal]

Additionally I noticed that the code runs well separately, but doesnt work if I dont put and end with between all the conditions. But this is the only issue i encounter and I would like to mark the thread as completed.

[sintek]

Your post 7 code has no bearing on your previous requirements????
Anyway, there is a much more simplistic approach to what you are trying to achieve in Post 7...I can only assist if you upload a sample file with data pertaining to these filters...

[zoepal]

Hey there,
I managed to solve the problem, I just put there the operator xland and it worked. But you said that there is a more simplistic approach for that case. I would like to know how you do it.
I attach you the file:

Thanks for the help,

[sintek]

Please Login or Register  to view this content.