Mixing
Delete all contents except for first row for specific sheets in Excel
1
Please assist. I have this code here and it works for first sheet however, does not execute in the 2nd sheet. I think i'm missing a sheet syntax. thanks.
Dim arrSheets As Variant, sht As Variant
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
Sheets(sht).UsedRange.Offset(1).ClearContents
Next sht
arrays excel vba
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
add a comment |
1
Please assist. I have this code here and it works for first sheet however, does not execute in the 2nd sheet. I think i'm missing a sheet syntax. thanks.
Dim arrSheets As Variant, sht As Variant
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
Sheets(sht).UsedRange.Offset(1).ClearContents
Next sht
arrays excel vba
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
What istl_1? These are not declared or not shown here
– urdearboy
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35
add a comment |
1
1
1
Please assist. I have this code here and it works for first sheet however, does not execute in the 2nd sheet. I think i'm missing a sheet syntax. thanks.
Dim arrSheets As Variant, sht As Variant
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
Sheets(sht).UsedRange.Offset(1).ClearContents
Next sht
arrays excel vba
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
Please assist. I have this code here and it works for first sheet however, does not execute in the 2nd sheet. I think i'm missing a sheet syntax. thanks.
Dim arrSheets As Variant, sht As Variant
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
Sheets(sht).UsedRange.Offset(1).ClearContents
Next sht
arrays excel vba
arrays excel vba
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
edited Nov 20 '18 at 1:13
Excelosaurus
2,1171715
2,1171715
asked Nov 19 '18 at 20:22
Al5145Al5145
155
asked Nov 19 '18 at 20:22
Al5145Al5145
155
asked Nov 19 '18 at 20:22
Al5145Al5145
155
155
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
What istl_1? These are not declared or not shown here
– urdearboy
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35
add a comment |
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
What istl_1? These are not declared or not shown here
– urdearboy
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
What is
tl_1? These are not declared or not shown here– urdearboy
Nov 19 '18 at 20:35
What is
tl_1? These are not declared or not shown here– urdearboy
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35
add a comment |
3 Answers
3
active
oldest
votes
1
Option Explicit
'This will do something to your target sheets,
'just make sure your "something" is what you want
Dim N As Long
Dim wsName As String
'Optional if included
For N = 1 To ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name
If wsName = "tl_1" or wsName = "tl_2" Then
ThisWorkbook.Worksheets(N).UsedRange.Offset(1).ClearContents
Else 'Do Nothing
End If
Next N
Note: This is very worksheet name dependent and users can change those if you are not the only user. You may want to CodeName your sheets (properties Explorer and change the Name) then pull the CodeName.
You can use an array to hold the sheets names but it seems like more trouble than its worth unless you are going to load the names into the array.
-WWC
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
|
show 5 more comments
3
My 2 cents: do not rely on worksheet names. Instead, work with their (Name) property as seen from the Visual Basic Editor. Note that you can give significant names yourself instead of the default Sheet1..N ones. This way, your code will survive modifications to the names as seen from Excel, in the tabs at the bottom.
Option Explicit
Public Sub DoTheClearThing()
Dim loopVariant As Variant
Dim loopWorksheet As Excel.Worksheet
Dim worksheetsToConsider As Variant
'Use the (Name) property, as seen from the Visual Basic Editor, of the worksheets you're interested in.
'The array below is not a string array; it is an array of worksheet objects.
worksheetsToConsider = Array(TL_1, TL_2)
'The For's variable must be a Variant; you can then Set it to a variable of the Worksheet type and enjoy Intellisense.
For Each loopVariant In worksheetsToConsider
Set loopWorksheet = loopVariant
loopWorksheet.UsedRange.Offset(1).ClearContents
Next
End Sub
Here's what the worksheets' (Name) properties look like in my test Excel workbook:
From within your workbook's code, you can directly interact with worksheet objects using their (Name) property. Avoid ThisWorkbook.Worksheets("tl_1").SomeMethod and directly go with TL_1.SomeMethod. By the way, a worksheet's (Name) property can be read by code using its CodeName property.
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
To clarify: "whenever possible" is "whenever the sheet exists inThisWorkbookat compile time"
– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
add a comment |
1
Worksheet.UsedRange does not always return the correct address. Consider the results of the test below:
Test Used Range
Sub Test()
Application.ScreenUpdating = False
Dim cell As Range, target As Range
Debug.Print "Data Range", "|"; "UsedRange"
Debug.Print String(24, "_")
With Worksheets.Add
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
With Worksheets.Add
.Range("A1:G10").Interior.Color = 65535
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
End Sub
Clear Non-Headers
Sub ClearNonHeaderRows()
Dim arrSheets As Variant, sht As Variant
Dim cell As Range
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
With Worksheets(sht).UsedRange
Set cell = .Find("*", .Cells(1, 1))
If Not cell Is Nothing Then
cell.Offset(1).Resize(.Cells.Rows.Count - cell.row + .Cells(1, 1).row).EntireRow.ClearContents
End If
End With
Next sht
End Sub
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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%2fstackoverflow.com%2fquestions%2f53382084%2fdelete-all-contents-except-for-first-row-for-specific-sheets-in-excel%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Option Explicit
'This will do something to your target sheets,
'just make sure your "something" is what you want
Dim N As Long
Dim wsName As String
'Optional if included
For N = 1 To ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name
If wsName = "tl_1" or wsName = "tl_2" Then
ThisWorkbook.Worksheets(N).UsedRange.Offset(1).ClearContents
Else 'Do Nothing
End If
Next N
Note: This is very worksheet name dependent and users can change those if you are not the only user. You may want to CodeName your sheets (properties Explorer and change the Name) then pull the CodeName.
You can use an array to hold the sheets names but it seems like more trouble than its worth unless you are going to load the names into the array.
-WWC
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
|
show 5 more comments
1
Option Explicit
'This will do something to your target sheets,
'just make sure your "something" is what you want
Dim N As Long
Dim wsName As String
'Optional if included
For N = 1 To ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name
If wsName = "tl_1" or wsName = "tl_2" Then
ThisWorkbook.Worksheets(N).UsedRange.Offset(1).ClearContents
Else 'Do Nothing
End If
Next N
Note: This is very worksheet name dependent and users can change those if you are not the only user. You may want to CodeName your sheets (properties Explorer and change the Name) then pull the CodeName.
You can use an array to hold the sheets names but it seems like more trouble than its worth unless you are going to load the names into the array.
-WWC
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
|
show 5 more comments
1
1
1
Option Explicit
'This will do something to your target sheets,
'just make sure your "something" is what you want
Dim N As Long
Dim wsName As String
'Optional if included
For N = 1 To ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name
If wsName = "tl_1" or wsName = "tl_2" Then
ThisWorkbook.Worksheets(N).UsedRange.Offset(1).ClearContents
Else 'Do Nothing
End If
Next N
Note: This is very worksheet name dependent and users can change those if you are not the only user. You may want to CodeName your sheets (properties Explorer and change the Name) then pull the CodeName.
You can use an array to hold the sheets names but it seems like more trouble than its worth unless you are going to load the names into the array.
-WWC
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
Option Explicit
'This will do something to your target sheets,
'just make sure your "something" is what you want
Dim N As Long
Dim wsName As String
'Optional if included
For N = 1 To ThisWorkbook.Sheets.Count
wsName = ThisWorkbook.Worksheets(N).Name
If wsName = "tl_1" or wsName = "tl_2" Then
ThisWorkbook.Worksheets(N).UsedRange.Offset(1).ClearContents
Else 'Do Nothing
End If
Next N
Note: This is very worksheet name dependent and users can change those if you are not the only user. You may want to CodeName your sheets (properties Explorer and change the Name) then pull the CodeName.
You can use an array to hold the sheets names but it seems like more trouble than its worth unless you are going to load the names into the array.
-WWC
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
edited Nov 19 '18 at 20:59
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
answered Nov 19 '18 at 20:35
Wookies-Will-CodeWookies-Will-Code
641217
641217
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
|
show 5 more comments
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
You commented this, but just wanted to doubly point out that this will execute on all Worksheets in the Workbook. From the OP's post, it looks like they only want to do this for the sheets called "tl_1" and "tl_2"
– ArcherBird
Nov 19 '18 at 20:41
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Thank you @ArcherBird, better to save him from deleting important data, added an if to only act on those two sheets . . .
– Wookies-Will-Code
Nov 19 '18 at 20:42
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
Wrote my answer as you posted yours, sorry. Well, even CodeNames can change, hence my approach; the code wouldn't compile anymore should a CodeName change, giving the dev a heads up.
– Excelosaurus
Nov 19 '18 at 21:15
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Excelosaurus code name can only be changed through the properties toolwindow in the VBE. If a user changes that, and it breaks,....they asked for it.
– Mathieu Guindon
Nov 20 '18 at 15:21
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
@Mathier Guindon Sure. I was referring to devs who, in a refactoring frenzy, might change CodeNames. The code most probably won't compile anymore, letting them know they must refactor the code as well. My users can't get to the properties toolwindow ;-)
– Excelosaurus
Nov 20 '18 at 16:07
|
show 5 more comments
3
My 2 cents: do not rely on worksheet names. Instead, work with their (Name) property as seen from the Visual Basic Editor. Note that you can give significant names yourself instead of the default Sheet1..N ones. This way, your code will survive modifications to the names as seen from Excel, in the tabs at the bottom.
Option Explicit
Public Sub DoTheClearThing()
Dim loopVariant As Variant
Dim loopWorksheet As Excel.Worksheet
Dim worksheetsToConsider As Variant
'Use the (Name) property, as seen from the Visual Basic Editor, of the worksheets you're interested in.
'The array below is not a string array; it is an array of worksheet objects.
worksheetsToConsider = Array(TL_1, TL_2)
'The For's variable must be a Variant; you can then Set it to a variable of the Worksheet type and enjoy Intellisense.
For Each loopVariant In worksheetsToConsider
Set loopWorksheet = loopVariant
loopWorksheet.UsedRange.Offset(1).ClearContents
Next
End Sub
Here's what the worksheets' (Name) properties look like in my test Excel workbook:
From within your workbook's code, you can directly interact with worksheet objects using their (Name) property. Avoid ThisWorkbook.Worksheets("tl_1").SomeMethod and directly go with TL_1.SomeMethod. By the way, a worksheet's (Name) property can be read by code using its CodeName property.
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
To clarify: "whenever possible" is "whenever the sheet exists inThisWorkbookat compile time"
– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
add a comment |
3
My 2 cents: do not rely on worksheet names. Instead, work with their (Name) property as seen from the Visual Basic Editor. Note that you can give significant names yourself instead of the default Sheet1..N ones. This way, your code will survive modifications to the names as seen from Excel, in the tabs at the bottom.
Option Explicit
Public Sub DoTheClearThing()
Dim loopVariant As Variant
Dim loopWorksheet As Excel.Worksheet
Dim worksheetsToConsider As Variant
'Use the (Name) property, as seen from the Visual Basic Editor, of the worksheets you're interested in.
'The array below is not a string array; it is an array of worksheet objects.
worksheetsToConsider = Array(TL_1, TL_2)
'The For's variable must be a Variant; you can then Set it to a variable of the Worksheet type and enjoy Intellisense.
For Each loopVariant In worksheetsToConsider
Set loopWorksheet = loopVariant
loopWorksheet.UsedRange.Offset(1).ClearContents
Next
End Sub
Here's what the worksheets' (Name) properties look like in my test Excel workbook:
From within your workbook's code, you can directly interact with worksheet objects using their (Name) property. Avoid ThisWorkbook.Worksheets("tl_1").SomeMethod and directly go with TL_1.SomeMethod. By the way, a worksheet's (Name) property can be read by code using its CodeName property.
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
To clarify: "whenever possible" is "whenever the sheet exists inThisWorkbookat compile time"
– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
add a comment |
3
3
3
My 2 cents: do not rely on worksheet names. Instead, work with their (Name) property as seen from the Visual Basic Editor. Note that you can give significant names yourself instead of the default Sheet1..N ones. This way, your code will survive modifications to the names as seen from Excel, in the tabs at the bottom.
Option Explicit
Public Sub DoTheClearThing()
Dim loopVariant As Variant
Dim loopWorksheet As Excel.Worksheet
Dim worksheetsToConsider As Variant
'Use the (Name) property, as seen from the Visual Basic Editor, of the worksheets you're interested in.
'The array below is not a string array; it is an array of worksheet objects.
worksheetsToConsider = Array(TL_1, TL_2)
'The For's variable must be a Variant; you can then Set it to a variable of the Worksheet type and enjoy Intellisense.
For Each loopVariant In worksheetsToConsider
Set loopWorksheet = loopVariant
loopWorksheet.UsedRange.Offset(1).ClearContents
Next
End Sub
Here's what the worksheets' (Name) properties look like in my test Excel workbook:
From within your workbook's code, you can directly interact with worksheet objects using their (Name) property. Avoid ThisWorkbook.Worksheets("tl_1").SomeMethod and directly go with TL_1.SomeMethod. By the way, a worksheet's (Name) property can be read by code using its CodeName property.
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
My 2 cents: do not rely on worksheet names. Instead, work with their (Name) property as seen from the Visual Basic Editor. Note that you can give significant names yourself instead of the default Sheet1..N ones. This way, your code will survive modifications to the names as seen from Excel, in the tabs at the bottom.
Option Explicit
Public Sub DoTheClearThing()
Dim loopVariant As Variant
Dim loopWorksheet As Excel.Worksheet
Dim worksheetsToConsider As Variant
'Use the (Name) property, as seen from the Visual Basic Editor, of the worksheets you're interested in.
'The array below is not a string array; it is an array of worksheet objects.
worksheetsToConsider = Array(TL_1, TL_2)
'The For's variable must be a Variant; you can then Set it to a variable of the Worksheet type and enjoy Intellisense.
For Each loopVariant In worksheetsToConsider
Set loopWorksheet = loopVariant
loopWorksheet.UsedRange.Offset(1).ClearContents
Next
End Sub
Here's what the worksheets' (Name) properties look like in my test Excel workbook:
From within your workbook's code, you can directly interact with worksheet objects using their (Name) property. Avoid ThisWorkbook.Worksheets("tl_1").SomeMethod and directly go with TL_1.SomeMethod. By the way, a worksheet's (Name) property can be read by code using its CodeName property.
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
edited Nov 19 '18 at 21:06
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
answered Nov 19 '18 at 20:54
ExcelosaurusExcelosaurus
2,1171715
2,1171715
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
To clarify: "whenever possible" is "whenever the sheet exists inThisWorkbookat compile time"
– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
add a comment |
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
To clarify: "whenever possible" is "whenever the sheet exists inThisWorkbookat compile time"
– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
2
2
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
What @Excelosaurus said: Use worksheets.CodeName whenever possible over worksheets.Name, you don't want users changing your worksheets names and junking up your code.
– Wookies-Will-Code
Nov 19 '18 at 21:00
1
1
To clarify: "whenever possible" is "whenever the sheet exists in
ThisWorkbook at compile time"– Mathieu Guindon
Nov 19 '18 at 21:37
To clarify: "whenever possible" is "whenever the sheet exists in
ThisWorkbook at compile time"– Mathieu Guindon
Nov 19 '18 at 21:37
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
Good point @Mathieu Guindon, it does need to be a pre-existing sheet.
– Wookies-Will-Code
Nov 20 '18 at 15:13
add a comment |
1
Worksheet.UsedRange does not always return the correct address. Consider the results of the test below:
Test Used Range
Sub Test()
Application.ScreenUpdating = False
Dim cell As Range, target As Range
Debug.Print "Data Range", "|"; "UsedRange"
Debug.Print String(24, "_")
With Worksheets.Add
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
With Worksheets.Add
.Range("A1:G10").Interior.Color = 65535
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
End Sub
Clear Non-Headers
Sub ClearNonHeaderRows()
Dim arrSheets As Variant, sht As Variant
Dim cell As Range
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
With Worksheets(sht).UsedRange
Set cell = .Find("*", .Cells(1, 1))
If Not cell Is Nothing Then
cell.Offset(1).Resize(.Cells.Rows.Count - cell.row + .Cells(1, 1).row).EntireRow.ClearContents
End If
End With
Next sht
End Sub
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
add a comment |
1
Worksheet.UsedRange does not always return the correct address. Consider the results of the test below:
Test Used Range
Sub Test()
Application.ScreenUpdating = False
Dim cell As Range, target As Range
Debug.Print "Data Range", "|"; "UsedRange"
Debug.Print String(24, "_")
With Worksheets.Add
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
With Worksheets.Add
.Range("A1:G10").Interior.Color = 65535
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
End Sub
Clear Non-Headers
Sub ClearNonHeaderRows()
Dim arrSheets As Variant, sht As Variant
Dim cell As Range
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
With Worksheets(sht).UsedRange
Set cell = .Find("*", .Cells(1, 1))
If Not cell Is Nothing Then
cell.Offset(1).Resize(.Cells.Rows.Count - cell.row + .Cells(1, 1).row).EntireRow.ClearContents
End If
End With
Next sht
End Sub
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
add a comment |
1
1
1
Worksheet.UsedRange does not always return the correct address. Consider the results of the test below:
Test Used Range
Sub Test()
Application.ScreenUpdating = False
Dim cell As Range, target As Range
Debug.Print "Data Range", "|"; "UsedRange"
Debug.Print String(24, "_")
With Worksheets.Add
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
With Worksheets.Add
.Range("A1:G10").Interior.Color = 65535
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
End Sub
Clear Non-Headers
Sub ClearNonHeaderRows()
Dim arrSheets As Variant, sht As Variant
Dim cell As Range
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
With Worksheets(sht).UsedRange
Set cell = .Find("*", .Cells(1, 1))
If Not cell Is Nothing Then
cell.Offset(1).Resize(.Cells.Rows.Count - cell.row + .Cells(1, 1).row).EntireRow.ClearContents
End If
End With
Next sht
End Sub
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
Worksheet.UsedRange does not always return the correct address. Consider the results of the test below:
Test Used Range
Sub Test()
Application.ScreenUpdating = False
Dim cell As Range, target As Range
Debug.Print "Data Range", "|"; "UsedRange"
Debug.Print String(24, "_")
With Worksheets.Add
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
With Worksheets.Add
.Range("A1:G10").Interior.Color = 65535
Set target = .Range("B2:D2")
target.Value = 1
Debug.Print target.Address, "|"; .UsedRange.Address
End With
End Sub
Clear Non-Headers
Sub ClearNonHeaderRows()
Dim arrSheets As Variant, sht As Variant
Dim cell As Range
arrSheets = Array("tl_1", "tl_2")
For Each sht In arrSheets
With Worksheets(sht).UsedRange
Set cell = .Find("*", .Cells(1, 1))
If Not cell Is Nothing Then
cell.Offset(1).Resize(.Cells.Rows.Count - cell.row + .Cells(1, 1).row).EntireRow.ClearContents
End If
End With
Next sht
End Sub
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
answered Nov 19 '18 at 23:27
TinManTinMan
2,196212
2,196212
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f53382084%2fdelete-all-contents-except-for-first-row-for-specific-sheets-in-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
sht is a Variant, it is not an array of worksheets. There is another way to step through the worksheets with out the array. See below.
– Wookies-Will-Code
Nov 19 '18 at 20:31
What is
tl_1? These are not declared or not shown here– urdearboy
Nov 19 '18 at 20:35
Those are sheet names is what he is going for, wrap the code below in an IF, looking at your worksheets(N).Name or worksheets(N).CodeName if you only want to act upon a few sheets out of many. I don't use arrays to iterate through the worksheets typically, or arrays of worksheet names, although I guess you could.
– Wookies-Will-Code
Nov 19 '18 at 20:35