Please note the code that I have late binding and trying to unmerge cells and then delete them.
I have an error that states: Run-time error '91': Object variable or With block variable not set.
on line: For Each cell In Range(sRange & ":" & sRange2) <--- obviously I'm aware that Dim cell as Object is not going to work and that I may have to set it to something.
I'm not sure the syntax that I need to change it though so that it will work.
The only code I could find to unmerge without late binding I could find was this one:
For Each Cell In Range("A1:Z10")
If Cell.MergeCells = True Then
But this is not done in Excel vba but Access VBA...
'sExcel is the full path of the Excel file.
Public Sub FixExcel(sExcel as String)
Dim xlApp As Object
Dim xlBook As Object
Dim xlSheet As Object
Dim cell As Object
Dim Range As Object
Set xlApp = CreateObject("Excel.Application")
'Opens Excel TEMPlate always from the C:\ to reduce traffic
Set xlBook = xlApp.Workbooks.Open(sExcel)
Dim k as Long
Dim sRange, sRange2 as String
k = 8
sRange = Col_Letter(k)
sRange2 = Col_Letter(16)
' deletes columns 8 through 16
For Each cell In Range(sRange & ":" & sRange2)
If cell.MergeCells = True Then
cell.EntireColumn.Delete Shift:=-4159 'xlToLeft
Set xlBook = Nothing
Set xlApp = Nothing
Function Col_Letter(ByVal ColumnNumber As Long) As String
If ColumnNumber > 26 Then
' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter
' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')
Col_Letter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
' Columns A-Z
Col_Letter = Chr(ColumnNumber + 64)