Delete ALL custom cell styles EXCEL
6
Is it possible to delete all the custom cell styles in a workbook ?
Without having to delete them all one by time.
microsoft-excel microsoft-excel-2010 vba styles
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
add a comment |
6
Is it possible to delete all the custom cell styles in a workbook ?
Without having to delete them all one by time.
microsoft-excel microsoft-excel-2010 vba styles
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53
add a comment |
6
6
6
4
Is it possible to delete all the custom cell styles in a workbook ?
Without having to delete them all one by time.
microsoft-excel microsoft-excel-2010 vba styles
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
Is it possible to delete all the custom cell styles in a workbook ?
Without having to delete them all one by time.
microsoft-excel microsoft-excel-2010 vba styles
microsoft-excel microsoft-excel-2010 vba styles
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
edited Nov 23 '18 at 14:47
PeterH
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
asked Feb 1 '18 at 16:46
PeterHPeterH
3,49332347
3,49332347
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53
add a comment |
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53
add a comment |
2 Answers
2
active
oldest
votes
6
Try this small VBA macro:
Sub StyleKiller()
Dim N As Long, i As Long
With ActiveWorkbook
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next i
End With
End Sub
This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
edited Jan 7 at 10:30
PeterH
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
add a comment |
2
Ok, this wasn't as hard to do as I first thought.
Bit messy as I don't often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles()
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style
Set MyBook = ActiveWorkbook
On Error Resume Next
For Each CurStyle In MyBook.Styles
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
Case Else
CurStyle.Delete
End Select
Next CurStyle
Set tempBook = Workbooks.Add
Application.DisplayAlerts = False
MyBook.Styles.Merge Workbook:=tempBook
Application.DisplayAlerts = True
tempBook.Close
End Sub
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
Actually I like the way you loop overStylesrather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,jis = 1 and then youDelete(1). (2) moves to (1), butjincrements to 2, so the next step isDelete(2), which is the old (3).For Eachis an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
|
show 3 more comments
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1291085%2fdelete-all-custom-cell-styles-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
6
Try this small VBA macro:
Sub StyleKiller()
Dim N As Long, i As Long
With ActiveWorkbook
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next i
End With
End Sub
This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
edited Jan 7 at 10:30
PeterH
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
add a comment |
6
Try this small VBA macro:
Sub StyleKiller()
Dim N As Long, i As Long
With ActiveWorkbook
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next i
End With
End Sub
This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
edited Jan 7 at 10:30
PeterH
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
add a comment |
6
6
6
Try this small VBA macro:
Sub StyleKiller()
Dim N As Long, i As Long
With ActiveWorkbook
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next i
End With
End Sub
This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
edited Jan 7 at 10:30
PeterH
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
Try this small VBA macro:
Sub StyleKiller()
Dim N As Long, i As Long
With ActiveWorkbook
N = .Styles.Count
For i = N To 1 Step -1
If Not .Styles(i).BuiltIn Then .Styles(i).Delete
Next i
End With
End Sub
This resolves the Builtin vs Custom issue. Note we run the loop backwards to avoid corrupting the loop index.
edited Jan 7 at 10:30
PeterH
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
edited Jan 7 at 10:30
PeterH
3,49332347
edited Jan 7 at 10:30
PeterH
3,49332347
edited Jan 7 at 10:30
PeterH
3,49332347
3,49332347
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
answered Feb 1 '18 at 17:00
Gary's StudentGary's Student
13.3k31729
13.3k31729
add a comment |
add a comment |
2
Ok, this wasn't as hard to do as I first thought.
Bit messy as I don't often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles()
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style
Set MyBook = ActiveWorkbook
On Error Resume Next
For Each CurStyle In MyBook.Styles
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
Case Else
CurStyle.Delete
End Select
Next CurStyle
Set tempBook = Workbooks.Add
Application.DisplayAlerts = False
MyBook.Styles.Merge Workbook:=tempBook
Application.DisplayAlerts = True
tempBook.Close
End Sub
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
Actually I like the way you loop overStylesrather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,jis = 1 and then youDelete(1). (2) moves to (1), butjincrements to 2, so the next step isDelete(2), which is the old (3).For Eachis an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
|
show 3 more comments
2
Ok, this wasn't as hard to do as I first thought.
Bit messy as I don't often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles()
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style
Set MyBook = ActiveWorkbook
On Error Resume Next
For Each CurStyle In MyBook.Styles
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
Case Else
CurStyle.Delete
End Select
Next CurStyle
Set tempBook = Workbooks.Add
Application.DisplayAlerts = False
MyBook.Styles.Merge Workbook:=tempBook
Application.DisplayAlerts = True
tempBook.Close
End Sub
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
Actually I like the way you loop overStylesrather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,jis = 1 and then youDelete(1). (2) moves to (1), butjincrements to 2, so the next step isDelete(2), which is the old (3).For Eachis an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
|
show 3 more comments
2
2
2
Ok, this wasn't as hard to do as I first thought.
Bit messy as I don't often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles()
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style
Set MyBook = ActiveWorkbook
On Error Resume Next
For Each CurStyle In MyBook.Styles
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
Case Else
CurStyle.Delete
End Select
Next CurStyle
Set tempBook = Workbooks.Add
Application.DisplayAlerts = False
MyBook.Styles.Merge Workbook:=tempBook
Application.DisplayAlerts = True
tempBook.Close
End Sub
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
Ok, this wasn't as hard to do as I first thought.
Bit messy as I don't often use vba; but this code will roll back to just the default styles:
Sub DefaultStyles()
Dim MyBook As Workbook
Dim tempBook As Workbook
Dim CurStyle As Style
Set MyBook = ActiveWorkbook
On Error Resume Next
For Each CurStyle In MyBook.Styles
Select Case CurStyle.Name
Case "20% - Accent1", "20% - Accent2", _
"20% - Accent3", "20% - Accent4", "20% - Accent5", "20% - Accent6", _
"40% - Accent1", "40% - Accent2", "40% - Accent3", "40% - Accent4", _
"40% - Accent5", "40% - Accent6", "60% - Accent1", "60% - Accent2", _
"60% - Accent3", "60% - Accent4", "60% - Accent5", "60% - Accent6", _
"Accent1", "Accent2", "Accent3", "Accent4", "Accent5", "Accent6", _
"Bad", "Calculation", "Check Cell", "Comma", "Comma [0]", "Currency", _
"Currency [0]", "Explanatory Text", "Good", "Heading 1", "Heading 2", _
"Heading 3", "Heading 4", "Input", "Linked Cell", "Neutral", "Normal", _
"Note", "Output", "Percent", "Title", "Total", "Warning Text"
Case Else
CurStyle.Delete
End Select
Next CurStyle
Set tempBook = Workbooks.Add
Application.DisplayAlerts = False
MyBook.Styles.Merge Workbook:=tempBook
Application.DisplayAlerts = True
tempBook.Close
End Sub
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
edited Jul 6 '18 at 7:03
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
answered Feb 1 '18 at 16:57
PeterHPeterH
3,49332347
3,49332347
Actually I like the way you loop overStylesrather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,jis = 1 and then youDelete(1). (2) moves to (1), butjincrements to 2, so the next step isDelete(2), which is the old (3).For Eachis an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
|
show 3 more comments
Actually I like the way you loop overStylesrather than my looping over an index.
– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,jis = 1 and then youDelete(1). (2) moves to (1), butjincrements to 2, so the next step isDelete(2), which is the old (3).For Eachis an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue
– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
Actually I like the way you loop over
Styles rather than my looping over an index.– Gary's Student
Feb 1 '18 at 17:07
Actually I like the way you loop over
Styles rather than my looping over an index.– Gary's Student
Feb 1 '18 at 17:07
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
Will have problems here, deleting a collection forwards.
– AJD
Jul 6 '18 at 8:03
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@AJD Hi, thanks for your comment, what problems could be caused from this ?
– PeterH
Jul 6 '18 at 9:16
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,
j is = 1 and then you Delete(1). (2) moves to (1), but j increments to 2, so the next step is Delete(2), which is the old (3). For Each is an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue– AJD
Jul 6 '18 at 21:25
@PeterH: deleting forwards means that items in the collection are renumbered. For example, in the loop,
j is = 1 and then you Delete(1). (2) moves to (1), but j increments to 2, so the next step is Delete(2), which is the old (3). For Each is an enumerating loop, so the same logic is used. There was a good explanation on StackOverflow somewhere - I can't find it, but these also explain: stackoverflow.com/questions/18858718/… , stackoverflow.com/questions/45585393/vba-loop-and-delete-issue– AJD
Jul 6 '18 at 21:25
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
@AJD Thanks for the info, the macro itself works as intended, I have used it on several workbooks since I posted it back in Feb
– PeterH
Jul 9 '18 at 7:09
|
show 3 more comments
draft saved
draft discarded
Thanks for contributing an answer to Super User!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1291085%2fdelete-all-custom-cell-styles-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Very easy with VBA
– Gary's Student
Feb 1 '18 at 16:53