Use variable to denote column letter in range
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
add a comment |
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:J
would then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify bothRange
andCells
with the workbook & worksheet name which can be done within aWith....End With
block.
– Darren Bartrup-Cook
Nov 12 '18 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address
.
– Darren Bartrup-Cook
Nov 12 '18 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.
– Pᴇʜ
Nov 12 '18 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47
add a comment |
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
Snippet of the code:
I copy from B, paste into A.
Set A = Workbooks("SwbA").Worksheets("SwsA").Range("A1:A10")
Set B = Workbooks("twbB").Worksheets("twsB").Range("G1:G10")
A.value = B.value
I want to be able to declare a variable such as
Dim col AS STRING
col = "A"
So I can do something to this effect -
Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&1:col10)
Please take note, I don't want to do this -
col = "A1:A10" '--> this works, for other reasons I want to manipulate Column letter only
Please help
excel vba excel-vba range
excel vba excel-vba range
edited Nov 13 '18 at 2:12
Selvam
asked Nov 12 '18 at 10:33
SelvamSelvam
51161324
51161324
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:J
would then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify bothRange
andCells
with the workbook & worksheet name which can be done within aWith....End With
block.
– Darren Bartrup-Cook
Nov 12 '18 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address
.
– Darren Bartrup-Cook
Nov 12 '18 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.
– Pᴇʜ
Nov 12 '18 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47
add a comment |
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columnsA:J
would then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify bothRange
andCells
with the workbook & worksheet name which can be done within aWith....End With
block.
– Darren Bartrup-Cook
Nov 12 '18 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the rangeDebug.Print Columns(1).Resize(, 10).Address
.
– Darren Bartrup-Cook
Nov 12 '18 at 10:46
You could useSet B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.
– Pᴇʜ
Nov 12 '18 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47
3
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:J
would then be: Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify both Range
and Cells
with the workbook & worksheet name which can be done within a With....End With
block.– Darren Bartrup-Cook
Nov 12 '18 at 10:40
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:J
would then be: Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify both Range
and Cells
with the workbook & worksheet name which can be done within a With....End With
block.– Darren Bartrup-Cook
Nov 12 '18 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address
.– Darren Bartrup-Cook
Nov 12 '18 at 10:46
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address
.– Darren Bartrup-Cook
Nov 12 '18 at 10:46
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.– Pᴇʜ
Nov 12 '18 at 11:19
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.– Pᴇʜ
Nov 12 '18 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47
add a comment |
2 Answers
2
active
oldest
votes
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
add a comment |
Column Issue
When you declare a column as variant you can use either the Letter or Number. The Cells
property is then appropriate to use.
Number of Rows
Sub NumberOfRows()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Number of Rows
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)
rngA.Value = rngB.Value
End Sub
Last Row
Sub LastRow()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Last Row
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))
rngA.Value = rngB.Value
End Sub
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
);
);
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%2f53260298%2fuse-variable-to-denote-column-letter-in-range%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
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
add a comment |
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
add a comment |
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
I can't see your image but you can do
Dim Col As String
Col = "A"
Dim r As Range
Set r = Range(Col & "1:" & Col & "10")
edited Nov 12 '18 at 15:40
Pᴇʜ
20.4k42650
20.4k42650
answered Nov 12 '18 at 14:28
Harassed DadHarassed Dad
2,8911612
2,8911612
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
add a comment |
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
HD, i added line for missing image. I followed you suggestion - Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col&"1:"&col"10"). It copy pastes to ALL columns, not limiting to col: A. But to the correct rows.
– Selvam
Nov 13 '18 at 2:03
1
1
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
You're missing an & Should be Set A = Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10")
– Harassed Dad
Nov 13 '18 at 13:42
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
HD, Typo error in my post. My actual code is as you stated - Workbooks("SwbA").Worksheets("SwsA").Range(col & "1:" & col & "10") . This copy paste to all columns for the same rows.
– Selvam
Nov 14 '18 at 1:09
add a comment |
Column Issue
When you declare a column as variant you can use either the Letter or Number. The Cells
property is then appropriate to use.
Number of Rows
Sub NumberOfRows()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Number of Rows
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)
rngA.Value = rngB.Value
End Sub
Last Row
Sub LastRow()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Last Row
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))
rngA.Value = rngB.Value
End Sub
add a comment |
Column Issue
When you declare a column as variant you can use either the Letter or Number. The Cells
property is then appropriate to use.
Number of Rows
Sub NumberOfRows()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Number of Rows
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)
rngA.Value = rngB.Value
End Sub
Last Row
Sub LastRow()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Last Row
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))
rngA.Value = rngB.Value
End Sub
add a comment |
Column Issue
When you declare a column as variant you can use either the Letter or Number. The Cells
property is then appropriate to use.
Number of Rows
Sub NumberOfRows()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Number of Rows
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)
rngA.Value = rngB.Value
End Sub
Last Row
Sub LastRow()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Last Row
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))
rngA.Value = rngB.Value
End Sub
Column Issue
When you declare a column as variant you can use either the Letter or Number. The Cells
property is then appropriate to use.
Number of Rows
Sub NumberOfRows()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Number of Rows
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Cells(cFirst, cCol1).Resize(cLast)
Set rngA = Workbooks(cW2).Worksheets(cW2).Cells(cFirst, cCol2).Resize(cLast)
rngA.Value = rngB.Value
End Sub
Last Row
Sub LastRow()
Const cW1 As String = "twbB" ' Source Sheet or Book
Const cW2 As String = "SwbA" ' Target Sheet or Book
Const cCol1 As Variant = "G" ' Source Column Letter/Number
Const cCol2 As Variant = "A" ' Target Column Letter/Number
Const cFirst As Long = 1 ' First Row
Const cLast As Long = 10 ' Last Row
Dim rngB As Range ' Source Range
Dim rngA As Range ' Target Range
Set rngB = Workbooks(cW1).Worksheets(cW1).Range(Cells(cFirst, cCol1), Cells(cFirst, cCol1))
Set rngA = Workbooks(cW2).Worksheets(cW2).Range(Cells(cFirst, cCol2), Cells(cFirst, cCol2))
rngA.Value = rngB.Value
End Sub
answered Dec 22 '18 at 16:14
VBasic2008VBasic2008
2,0502214
2,0502214
add a comment |
add a 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.
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%2f53260298%2fuse-variable-to-denote-column-letter-in-range%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
3
I don't want to add as a full answer as I know this must be a duplicate question. Declare your columns using numbers (col A=1, B=2, etc). An unqualified reference to columns
A:J
would then be:Debug.Print Range(Cells(1, 1), Cells(1, 10)).EntireColumn.Address
. Good practice says to qualify bothRange
andCells
with the workbook & worksheet name which can be done within aWith....End With
block.– Darren Bartrup-Cook
Nov 12 '18 at 10:40
Another way would be to take the first column, calculate how many extra columns you need and resize the range
Debug.Print Columns(1).Resize(, 10).Address
.– Darren Bartrup-Cook
Nov 12 '18 at 10:46
You could use
Set B = Workbooks("SwbB").Worksheets("SwsB").Range(A.Offset(ColumnOffset:=6).Address)
.– Pᴇʜ
Nov 12 '18 at 11:19
Thank you for your answers. I do not have the expertise to employ your suggestions. I am learning from this.
– Selvam
Nov 13 '18 at 4:47