Not able to delete column because of merged row in between

[rickyckc]

Hi All,

I have a script that is supposed to...

1) Change formula to values
2) Clear validation
3) Delete all 3 conditional format and
4) Delete column G

In my sheet, Row A3:Q3 are merged. Row A8:Q8 are also merged.

My problem is, if I do it manually, I am able to delete column G but if I run my script, column A to Q are deleted. What's wrong with the my script below ?

Sub FormA()
'
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
With Selection.Validation
.Delete
.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
:=xlBetween
.IgnoreBlank = True
.InCellDropdown = True
.ShowInput = True
.ShowError = True
End With
Selection.FormatConditions.Delete
Columns("G:G").Select
Range("G2").Activate
Selection.Delete Shift:=xlToLeft
Range("A2").Select
End Sub

 

[cgcamal]

Hi ricky,

Maybe trying only this:

Columns("G:G").Delete Shift:=xlToLeft

instead of this:

Columns("G:G").Select
  Range("G2").Activate
  Selection.Delete Shift:=xlToLeft

Hope this helps.

Regards

 

[Michael M]

Have you tried the script with the cells unmerged ??
Merged cells are an absolute nightmare.
try changing the merged cells to unmerged and then
Highlight A3:Q3 / Format / Cells /Alignment / Horizontal and select "Center Across Selection"
This will then appear to be merged.

Sub FormA()
'
Cells.Value = Cells.Value
    With Cells.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
    End With
Cells.FormatConditions.Delete
Columns("G:G").Delete Shift:=xlToLeft
End Sub

 

[rickyckc]

Hi cgcamal and Michael,

Thanks for your help and time. 'Columns("G:G").Delete Shift:=xlToLeft' works great !

Regards,

 

[cgcamal]

You're welcome. Nice to know it works ricky.

Regards.

 

[rickyckc]

Sorry, one more question please.

I have about 30 or 31 tabs to run this script. If I start from first tab, how to auto advance to next tab until the last tab and auto stops ?

My final script is below

Sub FormA()
'
' Keyboard Shortcut: Ctrl+u
'
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Selection.FormatConditions.Delete
    Columns("G:G").Delete Shift:=xlToLeft
    Columns("J:J").Delete Shift:=xlToLeft
    Columns("P:AU").Delete Shift:=xlToLeft
    Columns("AF:CC").Delete Shift:=xlToLeft
    Range("A2").Select
End Sub

 

[cgcamal]

ricky,

To iterate through all sheets from the first one until the last sheet try with

Sub MyMacro
Dim Sh as Integer

For Sh = 1 To Sheets.Count
    With Sheets(Sh)
        [COLOR=DarkGreen]'Your code[/COLOR]
         
[COLOR=Green]' Don't forget to put the dot in front of required code within the "With" block for each Sheet[/COLOR]
.Columns("G:G").Delete Shift:=xlToLeft 
     End With
Next
End Sub

Hope this helps.

Regards.

 

[rickyckc]

As in like this ? If 'Yes', I'm afraid it doesn't work. Just remains at tab I'm starting with and deleted off my columns G to CC. Btw, the tab I'm starting with is actually 2nd tab.

Sub MyMacro()
Dim Sh As Integer

For Sh = 1 To Sheets.Count
    With Sheets(Sh)

' Start of my script
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Selection.FormatConditions.Delete
    Columns("G:G").Delete Shift:=xlToLeft
    Columns("J:J").Delete Shift:=xlToLeft
    Columns("P:AU").Delete Shift:=xlToLeft
    Columns("AF:CC").Delete Shift:=xlToLeft
    Range("A2").Select
' End of my script

    End With
Next
End Sub

 

[rickyckc]

cgcamal,

Ignore my last post. Below is my current code. It did went thru all the tabs. The starting tab was done fine. However, the next till the last tab somehow didn't convert formula to value.

Sub MyMacro()
Dim Sh As Integer

For Sh = 1 To Sheets.Count
    With Sheets(Sh)
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    With Selection.Validation
        .Delete
        .Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
        :=xlBetween
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
    End With
    Selection.FormatConditions.Delete
    .Columns("G:G").Delete Shift:=xlToLeft
    .Columns("J:J").Delete Shift:=xlToLeft
    .Columns("P:AU").Delete Shift:=xlToLeft
    .Columns("AF:CC").Delete Shift:=xlToLeft
    Range("A2").Select
         End With
Next
End Sub

 

[Michael M]

Did you use my code in with Cesars changes ?
Also, put sh.activate before this line

sh.activate
With Sheets(Sh)