Paste a single value into filtered Visible Cells - large file

[dribj]

Hi
I have a large excel file (2007) 135k+ rows. I would like to paste a single value (like "ok") into hundreds of blank cells in a filtered column. I've tried Go to Special / Visible Cells only - however I get an error that the area is too complex.

I assume I have to use VBA. I've read through a couple of posts but can't seem to get it quite right.

Can someone help?
thanks
jill

 

[ZVI]

Hi Jill, welcome to the MrExcel!

Provide autofiltering, select any cell in the filtered column and run this macro (via ALT-F8)
The visible cells of that column will be populated with default "ok" string.

' Select any cell in filtered column to populate it by "ok"
Sub PopulateFilteredColumn()
 
  Dim ActCell As Range, Area As Range, Ok As String, Rng As Range
 
  ' Define the range
  Set ActCell = ActiveCell
  With ActiveSheet
    If .FilterMode Then
      Set Rng = Intersect(ActiveCell.EntireColumn, .AutoFilter.Range)
    End If
  End With
  If Rng Is Nothing Then
    MsgBox "Active cell is not in AutoFilter range", vbExclamation, "Try again"
    Exit Sub
  End If
  ' Exclude the title cell
  Set Rng = Rng.Resize(Rng.Rows.Count - 1).Offset(1)
 
  ' Ask for the filling string
  Rng.Select
  Ok = InputBox("Type the value for the selected visible cells", , "ok")
 
  ' Populate the visible cells of the range
  If Len(Ok) Then
    With Application
      .ScreenUpdating = False
      .EnableEvents = False
      For Each Area In Rng.Areas
        Area.SpecialCells(xlCellTypeVisible).Value = Ok
      Next
      .ScreenUpdating = True
      .EnableEvents = True
    End With
  End If
  ActCell.Select
 
End Sub

Regards

 

[dribj]

Hi Jill, welcome to the MrExcel!

Provide autofiltering, select any cell in the filtered column and run this macro (via ALT-F8)
The visible cells of that column will be populated with default "ok" string.

' Select any cell in filtered column to populate it by "ok"
Sub PopulateFilteredColumn()
 
  Dim ActCell As Range, Area As Range, Ok As String, Rng As Range
 
  ' Define the range
  Set ActCell = ActiveCell
  With ActiveSheet
    If .FilterMode Then
      Set Rng = Intersect(ActiveCell.EntireColumn, .AutoFilter.Range)
    End If
  End With
  If Rng Is Nothing Then
    MsgBox "Active cell is not in AutoFilter range", vbExclamation, "Try again"
    Exit Sub
  End If
  ' Exclude the title cell
  Set Rng = Rng.Resize(Rng.Rows.Count - 1).Offset(1)
 
  ' Ask for the filling string
  Rng.Select
  Ok = InputBox("Type the value for the selected visible cells", , "ok")
 
  ' Populate the visible cells of the range
  If Len(Ok) Then
    With Application
      .ScreenUpdating = False
      .EnableEvents = False
      For Each Area In Rng.Areas
        Area.SpecialCells(xlCellTypeVisible).Value = Ok
      Next
      .ScreenUpdating = True
      .EnableEvents = True
    End With
  End If
  ActCell.Select
 
End Sub

Regards

Wow - thanks so much. Nice touch with message box!! So - the error is the same (too complex)
"Microsoft Office Excel cannot create or use the data range reference because it is too complex. Try one or more of the following: 1. Use data that can be selected in one contiguous rectangle, 2. Use data from the same sheet.

So - maybe this file is so large, and the filtering is so disjointed - that I will not be able to place values in blank cells unless I manually paste them in (one by one)??
Again...thanks so much for replying!!!!