Nick G
04-11-2019, 10:10 PM
Hello all,
I found the below vba code on the same forum where one can unmerge and fill down the duplicates values. The problem is that my data has addresses which have pin code at the end and floor no in the beginning, so when it unmerge and autofills it down it increases the numbers like. The 1st floor becomes 2nd floor,3rd floor and like that at the beginning and the pin code increases from 400028 to 400029 etc.
The data is large so I can not do it manually. The below code is otherwise working perfectly the only thing I want is that I don't want the autofill to be incremental.
So is there any way I can make modification to the below code to just fill down anything without increasing the values in autofill. Please help.
Thanks in advance.
Public Sub ProcessData()
Dim cell As Range
Dim MergedCell As Range
Dim NumRows As Long
Dim NumCols As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
For Each cell In Range("A1").Resize( _
.Cells.SpecialCells(xlCellTypeLastCell).Row, _
.Cells.SpecialCells(xlCellTypeLastCell).Column)
If cell.MergeCells Then
NumRows = cell.MergeArea.Rows.Count
NumCols = cell.MergeArea.Columns.Count
cell.UnMerge
If NumRows > 1 Then cell.AutoFill cell.Resize(NumRows)
If NumCols > 1 Then cell.Resize(NumRows).AutoFill cell.Resize(NumRows, NumCols)
End If
Next cell
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub
I found the below vba code on the same forum where one can unmerge and fill down the duplicates values. The problem is that my data has addresses which have pin code at the end and floor no in the beginning, so when it unmerge and autofills it down it increases the numbers like. The 1st floor becomes 2nd floor,3rd floor and like that at the beginning and the pin code increases from 400028 to 400029 etc.
The data is large so I can not do it manually. The below code is otherwise working perfectly the only thing I want is that I don't want the autofill to be incremental.
So is there any way I can make modification to the below code to just fill down anything without increasing the values in autofill. Please help.
Thanks in advance.
Public Sub ProcessData()
Dim cell As Range
Dim MergedCell As Range
Dim NumRows As Long
Dim NumCols As Long
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
With ActiveSheet
For Each cell In Range("A1").Resize( _
.Cells.SpecialCells(xlCellTypeLastCell).Row, _
.Cells.SpecialCells(xlCellTypeLastCell).Column)
If cell.MergeCells Then
NumRows = cell.MergeArea.Rows.Count
NumCols = cell.MergeArea.Columns.Count
cell.UnMerge
If NumRows > 1 Then cell.AutoFill cell.Resize(NumRows)
If NumCols > 1 Then cell.Resize(NumRows).AutoFill cell.Resize(NumRows, NumCols)
End If
Next cell
End With
With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub