Hey,
I need help with creating an array from a range of cells
I currently have this code in place:
Option Explicit
Option Base 1
Sub FixMerge()
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer
Application.ScreenUpdating= False
ar = Array("E80", "E81", "E82", "E83","E84", "E85", "E86", "E87", "E88", "E89", "E90")
For i = 1 To UBound(ar)
On Error Resume Next
Set rng =Range(Range(ar(i)).MergeArea.Address)
With rng
.MergeCells = False
cw = .Cells(1).ColumnWidth
mw = 0
For Each cM In rng
cM.WrapText = True
mw = cM.ColumnWidth + mw
Next
mw = mw + rng.Cells.Count * 0.66
.Cells(1).ColumnWidth = mw
.EntireRow.AutoFit
rwht = .RowHeight
.Cells(1).ColumnWidth = cw
.MergeCells = True
.RowHeight = rwht
End With
Next i
Application.ScreenUpdating = True
End Sub
(not allowing me to put this in code, sorry)
As you can see, I individually listed each cell between E80 to E90. The code is working fine as is but I need to make the range much longer (E80:E220) however I’m having trouble properly inserting the range into the array. I tried:
Dim myArr As Variant
myArr = Range(“E80:E220”)
I’ve search all over the internet and apparently that should be working, but it is not. Can somebody here please help me properly set this up? It's probably something so stupid that I am missing, but I'm new to this stuff and just can't seem to figure it out...
Thank you
I need help with creating an array from a range of cells
I currently have this code in place:
Option Explicit
Option Base 1
Sub FixMerge()
Dim mw As Single
Dim cM As Range
Dim rng As Range
Dim cw As Double
Dim rwht As Double
Dim ar As Variant
Dim i As Integer
Application.ScreenUpdating= False
ar = Array("E80", "E81", "E82", "E83","E84", "E85", "E86", "E87", "E88", "E89", "E90")
For i = 1 To UBound(ar)
On Error Resume Next
Set rng =Range(Range(ar(i)).MergeArea.Address)
With rng
.MergeCells = False
cw = .Cells(1).ColumnWidth
mw = 0
For Each cM In rng
cM.WrapText = True
mw = cM.ColumnWidth + mw
Next
mw = mw + rng.Cells.Count * 0.66
.Cells(1).ColumnWidth = mw
.EntireRow.AutoFit
rwht = .RowHeight
.Cells(1).ColumnWidth = cw
.MergeCells = True
.RowHeight = rwht
End With
Next i
Application.ScreenUpdating = True
End Sub
(not allowing me to put this in code, sorry)
As you can see, I individually listed each cell between E80 to E90. The code is working fine as is but I need to make the range much longer (E80:E220) however I’m having trouble properly inserting the range into the array. I tried:
Dim myArr As Variant
myArr = Range(“E80:E220”)
I’ve search all over the internet and apparently that should be working, but it is not. Can somebody here please help me properly set this up? It's probably something so stupid that I am missing, but I'm new to this stuff and just can't seem to figure it out...
Thank you
Last edited: