Copy blank cells from the top "by excluding certain columns"

[nelsontan]

Hi, I have the code which able to fill the blank cells by copying the content from the top, basically the vba codes is running for all columns in worksheet, but I would like to exclude certain columns, what should I do with the following codes, for example to exclude column M, N, O.

'copy data if cell are blank for number value from top to bottom
    Application.ScreenUpdating = False
    
    With Sheets("GFL")
    
    For RowIndex = 1 To LastRowIndex
         For iCntr = 1 To UsedRng.Columns.Count
           If Not IsEmpty(Cells(RowIndex, iCntr).Value) Then
            If IsNumeric(Cells(RowIndex, iCntr).Value) = False Then
                If IsEmpty(Cells(RowIndex + 1, iCntr).Value) Then
                 Cells(RowIndex + 1, iCntr).Value = Cells(RowIndex, iCntr).Value
                End If
            End If
           End If
         Next iCntr
    Next RowIndex
    End With

 

[footoo]

Try this (untested) :

Application.ScreenUpdating = False
For RowIndex = 1 To LastRowIndex
    For iCntr = 1 To UsedRng.Columns.Count
        If Not Intersect(Columns(iCntr), [M:O]) Is Nothing Then GoTo nxt
        If Not IsEmpty(Cells(RowIndex, iCntr).Value) Then
            If IsNumeric(Cells(RowIndex, iCntr).Value) = False Then
                If IsEmpty(Cells(RowIndex + 1, iCntr).Value) Then
                    Cells(RowIndex + 1, iCntr).Value = Cells(RowIndex, iCntr).Value
                End If
            End If
        End If
nxt: Next iCntr
Next RowIndex

The With....End With is not doing anything so I've omitted it.

Your code is not very efficient. I am unable to suggest more efficient code without knowing more about your worksheet.

 

[footoo]

A slight improvement (but still not very efficient) :

Application.ScreenUpdating = False
For RowIndex = 1 To LastRowIndex
    For iCntr = 1 To UsedRng.Columns.Count
        If Not IsEmpty(Cells(RowIndex, iCntr).Value) Then
            If IsNumeric(Cells(RowIndex, iCntr).Value) = False Then
                If IsEmpty(Cells(RowIndex + 1, iCntr).Value) Then
                    Cells(RowIndex + 1, iCntr).Value = Cells(RowIndex, iCntr).Value
                End If
            End If
        End If
        If iCntr = 12 Then iCntr = 16
   Next iCntr
Next RowIndex

 

[footoo]

A bit more efficient :

Dim rng As Range, cel As Range
Set rng = Union([A:L], Range(Columns(16), Columns(Columns.Count)))
Set rng = Intersect(rng, ActiveSheet.UsedRange).Offset(1).SpecialCells(xlCellTypeBlanks)
Application.ScreenUpdating = False
For Each cel In rng
    If Not IsNumeric(cel(0)) Then cel = cel(0).Value
Next

 

[nelsontan]

Try this (untested) :

Application.ScreenUpdating = False
For RowIndex = 1 To LastRowIndex
    For iCntr = 1 To UsedRng.Columns.Count
        If Not Intersect(Columns(iCntr), [M:O]) Is Nothing Then GoTo nxt
        If Not IsEmpty(Cells(RowIndex, iCntr).Value) Then
            If IsNumeric(Cells(RowIndex, iCntr).Value) = False Then
                If IsEmpty(Cells(RowIndex + 1, iCntr).Value) Then
                    Cells(RowIndex + 1, iCntr).Value = Cells(RowIndex, iCntr).Value
                End If
            End If
        End If
nxt: Next iCntr
Next RowIndex

The With....End With is not doing anything so I've omitted it.

Your code is not very efficient. I am unable to suggest more efficient code without knowing more about your worksheet.

Thanks for the suggestion, it's works. It's kinda hard to explain all the scenario that in the worksheet due to the format of report generated from the system (e.g. a lot of blank column, rows, and need to fill in blank cells from top/right, and etc). I appreciate the help by the way.