score:6
welcome to stackoverflow :)
a1
can be written as range("j1").value
and d43
can be written as range("k1").value
. so "a1:d43"
can be written as range("j1").value & ":" & range("k1").value
simply replace range("a1:d43").select
with range(range("j1").value & ":" & range("k1").value).select
btw, avoid using the .select
. you may want to see how to avoid using select in excel vba
your code can be written as
range(range("j1").value & ":" & range("k1").value).copypicture _
appearance:=xlscreen, format:=xlbitmap
i am assuming that range("j1")
and range("k1")
are in the same sheet as from where you want to make the selection. if not then fully qualify the cells. for example
sheet1.range(sheet2.range("j1").value & ":" & sheet2.range("k1").value)
change sheet1
and sheet2
as applicable.
as jeeped showed in his post, you can also write the above as
sheet1.range(sheet2.range("j1").value, sheet2.range("k1").value).copypicture _
appearance:=xlscreen, format:=xlbitmap
score:1
try,
sub button3_click()
'
' button3_click macro
'
range(cells(1, "j").text, cells(1, "k").text).copypicture _
appearance:=xlscreen, format:=xlbitmap
end sub
score:1
another possibility, using join() function on an array made out of wanted range values:
option explicit
sub button3_click()
'
' button3_click macro
'
range(join(application.transpose(application.transpose(range("j1:k1").value)), ":")).copypicture _
appearance:=xlscreen, format:=xlbitmap
end sub
Source: stackoverflow.com
Related Query
- Using another cell value to reference a range
- Retrieve Cell Value from another workbook using a reference cell location
- Copying the cell value preserving the formatting from one cell to another in excel using VBA
- Excel VBA: how to add data validation list which contain comma included value without using reference of Range
- Excel Chart range based on the value in a cell in another sheet
- How to parameterize the range argument of a function using another cell's value
- Copy rows based on one cell value and in reference to another cell value and paste on a new sheet
- Replace part of a cell formula dynamically using value of another cell
- how to specify a range in vba using a cell value in excel
- Increase vlookup cell reference when another value changes
- Changing value of a cell in multiple worksheets from a range in another workbook
- How to get cell reference from range object in Excel using VB.net?
- Copy a static range on one sheet, and paste in a dynamic range in another sheet based on a single value in a cell
- Find cells within a range if one cell equals a criteria copy its value to another column
- Setting Excel cell value based on another cell value using VBA
- Create new Sheet for each cell in Range and rename using its value
- How to use COUNT/COUNTIF to count cell value of a given range in another sheet
- Copy Range From One Sheet Paste Part of Range In Same Sheet Based On Cell Value On Another Sheet
- Changing the value of a a cell if another cell is equal to a certain value using VBA
- Get cell value using named range in Excel VBA
- Iterate through populated rows, Match based on criteria, add value to specific cell in another sheet in Excel using VBA
- reference a cell from another worksheet using a variable
- Use Cell Value Reference in VBA to determine range
- How do I reference a cell using variables? Seemingly "correct" object range returns error 1004 VBA
- Using 2 sheets, if cell value matches, bring in Date value from another column
- Define range based on the value of another cell
- Using the value of one cell to locate another on a different sheet
- Conditional value entered in specific cell in a range, unlocks another specific cell in another range (VBA, Range Offset)
- Selecting an offset range after finding a cell that matches the value of another cell
- Define a range point using text value in cell
More Query from same tag
- VBA Array multiplication, elementwise without looping
- Excel Date Issue
- Get screen size similar to CursorPos (Excel VBA)
- Multiple find and replace in MS Word from a list in MS Excel: code does not work for embedded tables
- How to use named ranged as reference for validation
- Copy Data from Particular sheets and save in existing sheet"Draft"
- Excel VBA to create Visio Org Chart
- How can i apply this VBA concatenate to all worksheets?
- Use of variables embedded in a text in a cell
- How do I get Excel to only print sheets that have been populated with certain data, and exclude the printing of the one sheet?
- Autofill Runtime error 1004 - autofill dynamic range
- Query result in a variable
- VBA API call `MeassageBoxA` - keep it “on top” after Worksheet selection. Z option Hanging Hooky stuff?
- Delete all rows with autofilled value
- Does vba in word and excel uses different declaration?
- Need help returning random order multiple-choice answers for ms-access test bank
- How to continue the sequence of the unique numbers in the excel sheet after closing the userform?
- VBA Delete autofiltered rows in linked table
- how to display the rows and columns values in VBA for select query
- MS Access Web Browser Control not loading HTML content in VBA if Visible is set to False
- How to save selected item in an activeX dropdown in excel
- Correct way to select workbook in currently open spreadsheet
- Improving the speed of a nested loop where random numbers are generated
- Change the value of another cell
- Save DAO QueryDef to Temporary Query and Output Results to Excel
- VBA code to run a code at specific times
- Excel VBA - Expand all Columns Toggle?
- how to use match case in excel VBA
- How can I fix an Access Database missing references, libaries, and objects on Windows 7 Ulti 64bit
- Use VBA Script To Parse Full Name Into Parts