Filling a half-empty column with a formula in Excel

Question

I need a way to select a column containing thousands of empty cells down to the last row containing data, which is usually in a non-adjacent column, and then fill the selection with a formula. So far I have been using ctrl + ↓ to find the last row of a column I know is not empty and using that as a reference point to determine my range. Is there a way to do this more quickly in VBA? I'm quite new to VBA but my job would be a lot easier if I could do this. Thanks in advance! :)

do you have any code or snippet – Biswajit_86 Jun 08 '15 at 02:53 — Biswajit_86, Jun 08 '15 at 02:53

paul bica · Accepted Answer · 2017-07-16T16:25:42.153

Some reliable ways to find the last used cells containing values on the sheet:

.

Option Explicit
Sub lastUsedCells()         'optimised for performance
    Dim maxRow As Long
    Dim maxCol As Long
    With ActiveSheet
        If WorksheetFunction.CountA(.UsedRange) > 0 Then        'if sheet is not empty
            With .UsedRange 'restrict search area to UsedRange; (includes formats)

                maxRow = .Rows.Count + 1                        'last row in UsedRange
                maxCol = .Columns.Count + 1                     'last col in UsedRange

                'first cell not empty in col A
                If Len(.Cells(1, 1)) = 0 Then MsgBox .Cells(1, 1).End(xlDown).Row
                'first cell not empty in row 1
                If Len(.Cells(1, 1)) = 0 Then MsgBox .Cells(1, 1).End(xlToRight).Column
                MsgBox .Cells(maxRow, 1).End(xlUp).Row          'last row in column A
                MsgBox .Cells(1, maxCol).End(xlToLeft).Column   'last column in row 1
                MsgBox .Find( _
                            What:="*", _
                            After:=.Cells(1, 1), _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlPrevious).Row    'last row (longest col)
                MsgBox .Find( _
                            What:="*", _
                            After:=.Cells(1, 1), _
                            LookIn:=xlFormulas, _
                            SearchOrder:=xlByColumns, _
                            SearchDirection:=xlPrevious).Column 'last col (longest row)
            End With
        Else
            MsgBox "Sheet " & .Name & " is empty"
        End If
    End With
End Sub

More details: Finding last used cell in VBA

Ah, thank you! This is exactly what I've been looking for. :) I've been searching SO with the wrong phrase, haha. — slothy, Jun 08 '15 at 23:42

Filling a half-empty column with a formula in Excel

1 Answers1