copy and paste visible data in "M" Column to one cell above in excel VBA

[giriraj_dm]

Hi ,

Could anyone please help me in i want a macro file which will copy one cell above in Column "M"

1. the data will be Column "M" always.
2 the data may be in any cell in column "M"
3. where ever the data is it should copy one cell above in same column "M".

example : the if the data is in M10 it should copy to M9.

Thanks and regards.
Giriraj.

 

[Joe4]

Welcome to the Board!

Are you saying that you want to fill all blanks in column M with the value from the row just below the blank?
Might there be many blanks in a row (example M1 blank, M2 blank, M3 has data)?
If so, do you want to fill in ALL the blanks, or just the ones above a row with data?

 

[giriraj_dm]

Welcome to the Board!

Are you saying that you want to fill all blanks in column M with the value from the row just below the blank?
Might there be many blanks in a row (example M1 blank, M2 blank, M3 has data)?
If so, do you want to fill in ALL the blanks, or just the ones above a row with data?

yes ,there will be blanks where ever the data is there in M column it should copy to the above cell.

it may be in m10 ,m15,m20 like wise it should copy to the above cell.

Giriraj.

 

[Joe4]

If my assumptions in my previous post were correct, you only need this one line of code:

    Columns("M:M").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"

To see a non-VBA way of doing this, check out the first section here: https://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html#a2

 

[giriraj_dm]

If my assumptions in my previous post were correct, you only need this one line of code:

    Columns("M:M").SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "=R[1]C"

To see a non-VBA way of doing this, check out the first section here: https://www.extendoffice.com/documents/excel/771-excel-fill-blank-cells-with-value-above.html#a2

Hi Joe,Thanks for your reply, i was looking for in col m say M10 it should copy to M9 and if we data in m15 it should copy m14 and the rest should remain blank as it is ,it should not fill for the blank column.


regards
Giriraj

 

[Joe4]

Try this instead then:

Sub UpdateCells()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column M with an entry
    lastRow = Cells(Rows.Count, "M").End(xlUp).Row
    
'   Loop through all rows in column M
    For myRow = 1 To lastRow
'       Check to see if current row is blank and row below is populated
        If Cells(myRow, "M") = "" And Cells(myRow + 1, "M") <> "" Then
            Cells(myRow, "M") = Cells(myRow + 1, "M")
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

 

[giriraj_dm]

Try this instead then:

Sub UpdateCells()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
    
'   Find last row in column M with an entry
    lastRow = Cells(Rows.Count, "M").End(xlUp).Row
    
'   Loop through all rows in column M
    For myRow = 1 To lastRow
'       Check to see if current row is blank and row below is populated
        If Cells(myRow, "M") = "" And Cells(myRow + 1, "M") <> "" Then
            Cells(myRow, "M") = Cells(myRow + 1, "M")
        End If
    Next myRow
    
    Application.ScreenUpdating = True
    
End Sub

Thanks Joe you made my day I was looking for this code.

Regards
Girriaj

 

[Joe4]

You are welcome.