none
Excel VBA loop to find empty cell then replace adjacent rows by "Empty"

    Question

  • Hi all,

    I have Rondebruin's macro to loop through the column and delete empty cell.

    Sub ColumnE()
    
    Dim Firstrow As Long
        Dim LastRow As Long
        Dim Lrow As Long
        Dim CalcMode As Long
        Dim ViewMode As Long
    
        With Application
            CalcMode = .Calculation
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
    
        'We use the ActiveSheet but you can replace this with
        'Sheets("MySheet")if you want
        With ActiveSheet
    
            'We select the sheet so we can change the window view
            .Select
    
            'If you are in Page Break Preview Or Page Layout view go
            'back to normal view, we do this for speed
            ViewMode = ActiveWindow.View
            ActiveWindow.View = xlNormalView
    
            'Turn off Page Breaks, we do this for speed
            .DisplayPageBreaks = False
    
            'Set the first and last row to loop through
            Firstrow = .UsedRange.Cells(1).Row
            LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
    
            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = LastRow To Firstrow Step -1
    
                'We check the values in the A column in this example
                With .Cells(Lrow, "i")
    
                    If Not IsError(.Value) Then
    
                        If .Value = "" Then .EntireRow.Delete
                            '****I want to replace Cells in column J as "Test" instead of deleting the whole row.
    
                    End If
    
                End With
    
            Next Lrow
            
        End With
        
    
        
        ActiveWindow.View = ViewMode
        With Application
            .ScreenUpdating = True
            .Calculation = CalcMode
        End With
    
        
    End Sub

    My goal is to tweak the macro a bit, basically it is to change the value in adjacent cells.  Any one can help me with this?

    Thank you.


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Saturday, November 10, 2012 11:05 PM

Answers

  • The loop becomes:

            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = LastRow To Firstrow Step -1
                'Check the values in column I
                If Not IsError(.Cells(Lrow, "I").Value) Then
                    If .Cells(Lrow, "I").Value = "" Then
                        .Cells(Lrow, "J").Value = "Empty"
                    End If
                End If
            Next Lrow


    Regards, Hans Vogelaar


    Saturday, November 10, 2012 11:23 PM

All replies

  • The loop becomes:

            'We loop from Lastrow to Firstrow (bottom to top)
            For Lrow = LastRow To Firstrow Step -1
                'Check the values in column I
                If Not IsError(.Cells(Lrow, "I").Value) Then
                    If .Cells(Lrow, "I").Value = "" Then
                        .Cells(Lrow, "J").Value = "Empty"
                    End If
                End If
            Next Lrow


    Regards, Hans Vogelaar


    Saturday, November 10, 2012 11:23 PM
  • Once more,

    thank you very much Hans.


    Please do not forget to click “Vote as Helpful” if the reply helps/directs you toward your solution and or "Mark as Answer" if it solves your question. This will help to contribute to the forum.

    Saturday, November 10, 2012 11:42 PM