Autofill not working properly

[Wissamsaliba]

Hello, I am working on a large data set but recently started receiving smaller quantity of data. I used to use autofill a lot with filtered data but it seems if I have only 1 row of filtered data and use autofill it gives me the old image(I am changing the value inside the cell). Is there a way to write a code that if it counts 1 row to just replace the cells without autofill? below you can find my code that works properly if i have multiple rows but not 1 row.


ActiveSheet.Range("$A$1:$CB$627689").AutoFilter Field:=38, Criteria1:= _
"Max"
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 23).Select
ActiveCell.FormulaR1C1 = "Others"
Range(Selection, "W" & lRow).Select
Selection.FillDown

 

[Peter_SSs]

Welcome to the MrExcel board!

Selecting cells/ranges is rarely required in vba and is a relatively slow process so it is a good idea to learn how to avoid that when possible.
Filling one cell and then FillDown is also not often the best way to populate a column as it can usually be populated directly.

Finally, you have not told us what 'lRow' is or how a value is assigned to it. In the suggested code below I have guessed that lRow represents the last row on your sheet that contains data, rather than your data actually going to row 627,689.

See if this replacement code is any use to you.

With Range("A1:CB" & lRow)
  .AutoFilter Field:=38, Criteria1:="Max"
  If .Columns(1).SpecialCells(xlVisible).Count > 1 Then  .Columns(23).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Others"
End With

BTW, when posting vba code in the forum, please use the available code tags. My signature block below has more details/links.

 

[Wissamsaliba]

Amazing!!! And thank you for your advice! The codes works great! May you suggest a forum or guide to decrease the autofill?

 

[Peter_SSs]

Amazing!!! And thank you for your advice! The codes works great!

You're welcome. Thanks for the follow-up.

May you suggest a forum or guide to decrease the autofill?

I learnt the majority of what I know by hanging around in this forum a lot.

 

[Wissamsaliba]

Hello i just got with a problem in this code, it isn't counting the number of visible rows... If i don't have the exception it is giving me an error may some1 help please?

 

[Peter_SSs]

Hello i just got with a problem in this code, it isn't counting the number of visible rows...

What exactly do you mean by "it isn't counting the number of visible rows"

If i don't have the exception it is giving me an error may some1 help please?

What do you mean by "the exception"?

What is the full error message that are you getting and what line of code is causing that error?

 

[Wissamsaliba]

You gave me these lines to input them in my code

With Range("A1:CB" & lRow)
.AutoFilter Field:=38, Criteria1:="Max"
If .Columns(1).SpecialCells(xlVisible).Count > 1 Then .Columns(23).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Others"
End With

but what is happening is that columns(1).specialcells(xvisible).count >1 is taking automatically the value of 12 and not the count of visible cells... so the if statement will not skip if there is 0 or 1 count of rows.

 

[Peter_SSs]

Perhaps you do not have the correct value for lRow?
As I pointed out before ..

you have not told us what 'lRow' is or how a value is assigned to it.

If you gave us some sample data to test with using XL2BB and also posted your full code, it would most likely be easier to try to solve the problem.

 

[Wissamsaliba]

lRow is the total number of rows of all my excel not the filtered value. And what exactly do you need from my code so that you can help me? I am a bit confused

 

[Peter_SSs]

And what exactly do you need from my code so that you can help me? I am a bit confused

If you gave us some sample data to test with using XL2BB and also posted your full code, it would most likely be easier to try to solve the problem.