Deleting Two Columns Simultaneously
The following code deletes Column J only:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells(xlCellTypeVisible)) _
= Application.WorksheetFunction.Sum(Range("L:L").SpecialCells( _
xlCellTypeVisible)) Then
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
End If
However, I would like to delete both columns J
and L
at the same time. How can I do this?
excel vba excel-vba if-statement basic
add a comment |
The following code deletes Column J only:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells(xlCellTypeVisible)) _
= Application.WorksheetFunction.Sum(Range("L:L").SpecialCells( _
xlCellTypeVisible)) Then
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
End If
However, I would like to delete both columns J
and L
at the same time. How can I do this?
excel vba excel-vba if-statement basic
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)
– TinMan
Nov 23 at 5:24
add a comment |
The following code deletes Column J only:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells(xlCellTypeVisible)) _
= Application.WorksheetFunction.Sum(Range("L:L").SpecialCells( _
xlCellTypeVisible)) Then
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
End If
However, I would like to delete both columns J
and L
at the same time. How can I do this?
excel vba excel-vba if-statement basic
The following code deletes Column J only:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells(xlCellTypeVisible)) _
= Application.WorksheetFunction.Sum(Range("L:L").SpecialCells( _
xlCellTypeVisible)) Then
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
End If
However, I would like to delete both columns J
and L
at the same time. How can I do this?
excel vba excel-vba if-statement basic
excel vba excel-vba if-statement basic
edited Nov 23 at 3:18
K.Dᴀᴠɪs
6,857112139
6,857112139
asked Nov 23 at 2:39
chee seng ng
248
248
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)
– TinMan
Nov 23 at 5:24
add a comment |
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)
– TinMan
Nov 23 at 5:24
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)– TinMan
Nov 23 at 5:24
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)– TinMan
Nov 23 at 5:24
add a comment |
2 Answers
2
active
oldest
votes
You can easily modify your current code using Union()
.
Change this line:
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
to this
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
This will take both ranges and delete them simultaneously.
Your code block should now look as follows:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
End If
I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:
With ThisWorkbook.Worksheets(1)
If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
.Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Where you would replace the 1
in Worksheets(1)
to either the:
- Correct worksheet index number, or
- The name of the worksheet, surrounded by double-quotes (ex.
.Worksheets("Sheet1")
)
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing theWith ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.
– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
|
show 4 more comments
You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:
Option Explicit
Public Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
With ws 'Your sheet name goes here
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
End Sub
The first argument passed to AGGREGATE is Function_num
. 9
is SUM
. The second argument is Options
. 3
is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.
Edit:
If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.
Public Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Next
End Sub
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
|
show 1 more 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
});
}
});
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%2f53440036%2fdeleting-two-columns-simultaneously%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
You can easily modify your current code using Union()
.
Change this line:
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
to this
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
This will take both ranges and delete them simultaneously.
Your code block should now look as follows:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
End If
I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:
With ThisWorkbook.Worksheets(1)
If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
.Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Where you would replace the 1
in Worksheets(1)
to either the:
- Correct worksheet index number, or
- The name of the worksheet, surrounded by double-quotes (ex.
.Worksheets("Sheet1")
)
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing theWith ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.
– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
|
show 4 more comments
You can easily modify your current code using Union()
.
Change this line:
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
to this
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
This will take both ranges and delete them simultaneously.
Your code block should now look as follows:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
End If
I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:
With ThisWorkbook.Worksheets(1)
If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
.Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Where you would replace the 1
in Worksheets(1)
to either the:
- Correct worksheet index number, or
- The name of the worksheet, surrounded by double-quotes (ex.
.Worksheets("Sheet1")
)
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing theWith ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.
– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
|
show 4 more comments
You can easily modify your current code using Union()
.
Change this line:
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
to this
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
This will take both ranges and delete them simultaneously.
Your code block should now look as follows:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
End If
I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:
With ThisWorkbook.Worksheets(1)
If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
.Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Where you would replace the 1
in Worksheets(1)
to either the:
- Correct worksheet index number, or
- The name of the worksheet, surrounded by double-quotes (ex.
.Worksheets("Sheet1")
)
You can easily modify your current code using Union()
.
Change this line:
Columns("J:J").Delete Shift:=xlToLeft 'instead of .Select and Selection.
to this
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
This will take both ranges and delete them simultaneously.
Your code block should now look as follows:
If Application.WorksheetFunction.Sum(Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(Columns("J"), Columns("L")).Delete Shift:=xlToLeft
End If
I would also add that it would be a good idea to make sure you are deleting the columns in the correct worksheet. Please be explicit and always qualify your range with at the very least your worksheet, your workbook wouldn't be a bad idea too:
With ThisWorkbook.Worksheets(1)
If Application.WorksheetFunction.Sum(.Range("J:J").SpecialCells( _
xlCellTypeVisible)) = Application.WorksheetFunction.Sum( _
.Range("L:L").SpecialCells(xlCellTypeVisible)) Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Where you would replace the 1
in Worksheets(1)
to either the:
- Correct worksheet index number, or
- The name of the worksheet, surrounded by double-quotes (ex.
.Worksheets("Sheet1")
)
edited Nov 23 at 4:41
answered Nov 23 at 2:44
K.Dᴀᴠɪs
6,857112139
6,857112139
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing theWith ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.
– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
|
show 4 more comments
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing theWith ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.
– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
Weird...it did not work but no error message either.
– chee seng ng
Nov 23 at 3:01
It did for me. Did you use the code block containing the
With ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.– K.Dᴀᴠɪs
Nov 23 at 3:02
It did for me. Did you use the code block containing the
With ThisWorkbook.Worksheets(1)
statement? Because you may have just deleted the wrong columns from another worksheet if you didn't read the two bullet points under that code block I posted and made the adjustments I recommended.– K.Dᴀᴠɪs
Nov 23 at 3:02
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
I dont really get your first line "With ThisWorkbook.Worksheets(1)"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
Without using it. I have a "compile error: Invalid or unqualified reference"
– chee seng ng
Nov 23 at 4:00
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
So you still haven't read the two bullet points under the code block; I will update my answer - just tell me what is the complete name of the worksheet you are using this code on?
– K.Dᴀᴠɪs
Nov 23 at 4:10
|
show 4 more comments
You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:
Option Explicit
Public Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
With ws 'Your sheet name goes here
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
End Sub
The first argument passed to AGGREGATE is Function_num
. 9
is SUM
. The second argument is Options
. 3
is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.
Edit:
If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.
Public Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Next
End Sub
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
|
show 1 more comment
You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:
Option Explicit
Public Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
With ws 'Your sheet name goes here
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
End Sub
The first argument passed to AGGREGATE is Function_num
. 9
is SUM
. The second argument is Options
. 3
is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.
Edit:
If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.
Public Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Next
End Sub
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
|
show 1 more comment
You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:
Option Explicit
Public Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
With ws 'Your sheet name goes here
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
End Sub
The first argument passed to AGGREGATE is Function_num
. 9
is SUM
. The second argument is Options
. 3
is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.
Edit:
If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.
Public Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Next
End Sub
You can use EVALUATE and AGGREGATE to check if the sums in the two columns match:
Option Explicit
Public Sub test()
Dim ws As Worksheet
Set ws = ThisWorkbook.Worksheets("Sheet2")
With ws 'Your sheet name goes here
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
End Sub
The first argument passed to AGGREGATE is Function_num
. 9
is SUM
. The second argument is Options
. 3
is Ignore hidden rows, error values, nested SUBTOTAL and AGGREGATE functions
. The advantage of using AGGREGATE is you can specify different options for the sum conditions e.g. ignore errors as well as hidden rows.
Edit:
If using in a loop over worksheets use your worksheet variable from the loop in the With statement to ensure your ranges have a parent sheet specified. It will make your code less bug prone.
Public Sub test()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
If .Evaluate("AGGREGATE(9,3,J:J)") = .Evaluate("AGGREGATE(9,3,L:L)") Then
Union(.Columns("J"), .Columns("L")).Delete Shift:=xlToLeft
End If
End With
Next
End Sub
edited Nov 28 at 6:35
answered Nov 23 at 6:25
QHarr
29.7k81841
29.7k81841
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
|
show 1 more comment
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
I tried using your code but it shows error 'Run-time error '9': Subscript out of range
– chee seng ng
Nov 28 at 1:21
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
Did you change the sheet name appropriately?
– QHarr
Nov 28 at 4:52
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
I did not, as I need the code to be generic so I can use it for next wksht. Anyway it's resolved already. Thank you for your help!
– chee seng ng
Nov 28 at 6:25
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
No worries but I will mention that you should mention info like that in the question. Also, you can be "generic" and accurate. If you rely on activesheet references by not using a parent sheet then you are leaving yourself wide open to bugs. If you are looping sheets then simply use your worksheet variable in the loop as the parent. For Each ws in .... With ws...... then your code is more robust.
– QHarr
Nov 28 at 6:32
1
1
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
Thanks for your teaching, I will definitely look into this in the near future. As I just started learn VBA and Macro, I just hope to make the code work so I can understand the basic.
– chee seng ng
Nov 28 at 6:44
|
show 1 more comment
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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.
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%2f53440036%2fdeleting-two-columns-simultaneously%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
Range("L:L,J:J").Delete
will work. This is the kind of thing that you can learn from recording a macro. You should watch: Excel VBA Introduction Part 5 - Selecting Cells (Range, Cells, Activecell, End, Offset)– TinMan
Nov 23 at 5:24