Using another cell value to reference a range

Question

I have a work sheet that has some data in it. To simplify adding it into an email, I've created some code that upon a button press would select a range of cells and save it as an image to upload into email. The current code is the following:

Sub Button3_Click()
'
' Button3_Click Macro
'

'
Range("A1:D43").Select
ActiveWindow.SmallScroll Down:=-39
Selection.CopyPicture Appearance:=xlScreen, Format:=xlBitmap
End Sub

I want to use a different cell to reference the range. So for example in cell J1 I would insert "A1", and in sell K1 I would insert "D43" to find my range so the range could be updated without having to edit the code. Is this possible? Thanks!

Answer 1

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

Answer 2

try,

Sub Button3_Click()
'
' Button3_Click Macro
'
    Range(cells(1, "J").text, cells(1, "K").text).CopyPicture _
       Appearance:=xlScreen, Format:=xlBitmap

End Sub

Answer 3

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