I need your valuable help so that the following macro inserts columns in the missing numbers horizontally (in a row) of a selected range.
What happens is that the following macro does not insert the columns when there are other adjacent ranges on the left or right side of the selected range.
Sub InsertColsForMissingNumbers()
Dim rngMyRange As Range
Dim x As String
Dim y As Integer
Dim i As Long, j As Long
Set rngMyRange = Selection
x = rngMyRange.Cells(1, 1).Address
z = rngMyRange.Cells(rngMyRange.Rows.Count, rngMyRange.Columns.Count).Address
y = Range(x).Row
MsgBox "Primer rango: " & x
MsgBox "Segundo rango: " & z
MsgBox "Número de fila: " & y
MsgBox "Primer valor: " & Range(x).Value
MsgBox "Segundo valor: " & Range(z).Value
For i = Range(x).Value To Range(z).Value
j = Cells(y, i + 1) - Cells(y, i)
If j > 1 Then
Columns(i + 1).Resize(, j - 1).EntireColumn.Insert Shift:=xlToRight
i = i + j - 1
End If
Next i
End Sub
What is wrong that doesn't insert the columns?
What happens is that the following macro does not insert the columns when there are other adjacent ranges on the left or right side of the selected range.
Sub InsertColsForMissingNumbers()
Dim rngMyRange As Range
Dim x As String
Dim y As Integer
Dim i As Long, j As Long
Set rngMyRange = Selection
x = rngMyRange.Cells(1, 1).Address
z = rngMyRange.Cells(rngMyRange.Rows.Count, rngMyRange.Columns.Count).Address
y = Range(x).Row
MsgBox "Primer rango: " & x
MsgBox "Segundo rango: " & z
MsgBox "Número de fila: " & y
MsgBox "Primer valor: " & Range(x).Value
MsgBox "Segundo valor: " & Range(z).Value
For i = Range(x).Value To Range(z).Value
j = Cells(y, i + 1) - Cells(y, i)
If j > 1 Then
Columns(i + 1).Resize(, j - 1).EntireColumn.Insert Shift:=xlToRight
i = i + j - 1
End If
Next i
End Sub
What is wrong that doesn't insert the columns?