Autofill not working properly

Wissamsaliba

New Member
Joined
Feb 4, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
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
 

Wissamsaliba

New Member
Joined
Feb 4, 2022
Messages
6
Office Version
  1. 2016
Platform
  1. Windows
If you want i tested some other things and it worked but the autofill is driving me crazy! I tried to add this part of the code that i obtained from you "
.Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Lucky" "
but it is giving me an error

this is the full code


VBA Code:
 Dim xCount As Long
Dim xCell As range
Application.Volatile
lRow = range("A1").End(xlDown).Row
For Each xCell In range("A1:A" & lRow)
     If (Not xCell.EntireRow.Hidden) And (Not xCell.EntireColumn.Hidden) Then
          xCount = xCount + 1
     End If
Next
   If xCount > 1 Then .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Lucky"
   Else
   
   End If
   
 End Sub
 
Upvote 0

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
58,550
Office Version
  1. 365
Platform
  1. Windows
this is the full code
It cannot be, it does not compile.

Firstly, it does not have a procedure name.
Secondly it has unqualified references like
Rich (BB code):
If xCount > 1 Then .Columns(4).Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlVisible).Value = "Lucky"

Also, you have not provided any sample data to test it against.


I tried to add this part of the code that i obtained from you
The code you posted has no resemblance to the code I suggested earlier.
 
Upvote 0

Forum statistics

Threads
1,186,798
Messages
5,959,834
Members
438,452
Latest member
Poor Dave

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top