Select/fill visible cells

Tested in Excel 365 (16.8730.2046) 64-bit

 
 
  1. Public Sub WriteVisibleCells(TableRange As String, Optional RangeKey As Integer = 1)
  2.     Dim Rng As Range
  3.     Dim RowCount, LastCol, LastRow
  4.    
  5.     LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
  6.     LastRow = Range("A" & rows.Count).End(xlUp).Row
  7.    
  8.     ' add some filter
  9.    ActiveSheet.Range(TableRange).AutoFilter Field:=2, Criteria1:="Sold"
  10.     ActiveSheet.Range(TableRange).AutoFilter Field:=3, Criteria1:="<>Sold"
  11.    
  12.     Set Rng = ActiveSheet.AutoFilter.Range ' get the range of the filtered data
  13.    RowCount = Rng.Columns(RangeKey).SpecialCells(xlCellTypeVisible).Count - 1
  14.     If RowCount > 0 Then
  15.         fvr = rows("2:" & LastRow).SpecialCells(xlCellTypeVisible).Row     ' get the first visible row
  16.        lvr = Range("A" & rows.Count).End(xlUp).Row    ' get the last visible row
  17.        
  18.         Cells(fvr, LastCol + 1).Select
  19.         ActiveCell = "Sold"    ' add comment
  20.        If RowCount > 1 Then ' if more than 1 row you can use fill down*
  21.            Range(Cells(fvr, LastCol + 1), Cells(lvr, LastCol + 1)).SpecialCells(xlVisible).Select ' select the visible cells
  22.            Selection.FillDown
  23.         End If
  24.     End If
  25.     Range("a1").Select
  26.     ActiveSheet.ShowAllData
  27.    
  28. '* fill down doesn't work smooth on large file, let's say >1500 lines. Consider using FOR
  29. End Sub
  30. ' use like this:
  31. Sub main()
  32.     Call WriteVisibleCells("A1:D13")
  33. End Sub

You can use the data sample below to test and modify the code:

Name Status Comment Price
asset 1 stock new 32966
asset 2 stock new 31342
asset 3 sold new 24438
asset 4 sold sold 36108
asset 5 stock new 79120
asset 6 stock new 79120
asset 7 sold new 95954
asset 8 stock new 74413
asset 9 stock new 13834
asset 10 sold new 16681
asset 11 stock new 86
asset 12 stock new 165

Add a Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.