Hi ExcelMonkey,

The line:

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1,
tbl.Columns.Count).Select

merely selects a subset of the tbl range; it does not change tbl.

Consequently, when you later select tbl, you select the original tbl range,
which has not changed.

Conversely, my in my code:

Set rng = Range("A2").CurrentRegion
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
rng.Select

the rangg which corresponds to rng is changed by my second line assignment,
and, consequently, the altered range is selected in line 3.

I used the rng variable. in accordance with your original code. By all means
change rng in my code to tbl.

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
So this code worked for me:

Set tbl = Worksheets("Template").Range("A2").CurrentRegion
tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1,
tbl.Columns.Count).Select

However, I later want to copy the data in this range. I do the following:

tbl.Copy

However when i paste it. I get the entire range including Row 1. I was
using the resize method as I thought I would adjust the range I wanted to
copy. So although it resized it and selected this resized range, it
copies
the original. How is this?????


Thanks




"Norman Jones" wrote:

Hi ExcelMonkey,

Or:

Dim rng As Range

Set rng = Range("A2").CurrentRegion
Set rng = rng.Offset(1).Resize(rng.Rows.Count - 1)
rng.Select



---
Regards,
Norman



"Norman Jones" wrote in message
...
Hi ExcelMonkey,

Perhaps:

Rng.CurrentRegion.Select

---
Regards,
Norman



"ExcelMonkey" wrote in message
...
That doesn't work either. Note I have replaced my lines of code with
yours
below. Interestingly enough when I go to the immediate window I get:

?rng.End(xlToright).Address
$G$2
?rng.End(xlDown).Address
$A$24

This seems to make sense. It seems to be failing on .Select part of
the
statment.



Private Sub CommandButton1_Click()
Dim FName As Variant
Dim ExtractedFileName As Variant
Dim ThisFileName As Variant
Dim FirstEmptyRow As Double
Dim rng As Range

ThisFileName = ThisWorkbook.Name

FName = Application.GetOpenFilename("A*ll files (*.*),*.*")
If FName = False Then
' user clicked cancel
Else

Workbooks.Open Filename:=FName

'Extract file Name from full path
ExtractedFileName = sFileName(FName)
Windows(ExtractedFileName).Activate

'Select First Cell in Target file sheet
Worksheets("Template").Range("A2").Select

'Choose Used Range of target sheet
Set rng = Worksheets("Template").Range("A2")
Range(rng, rng.End(xlDown).End(xlToRight)).Select

'Range(Selection, Selection.End(xlDown)).Select
'Range(Selection, Selection.End(xlToRight)).Select

End If
End Sub

Function sFileName(sFullname As Variant) As String
If InStrRev(sFullname, "\") = 0 Then
sFileName = sFullname
Else
sFileName = Mid$(sFullname, _
InStrRev(sFullname, "\") + 1)
End If
End Function



"Mangesh Yadav" wrote:

Your code works for me.

You could also use:

Set rng = Worksheets("Sheet1").Range("A2")
Range(rng, rng.End(xlDown).End(xlToRight)).Select


Mangesh



"ExcelMonkey" wrote in
message
...
I am trying to select cell A2 and the SHIFT-CNTL-Right and
SHIFT-CNTL-Down.
When I recored the macro the code is as seen below under the
comment
'Choose Used Range of target sheet. However when I try to run it,
I
get
an
Run Time Error 1004. ("Method Range of Object _Worksheet failed")


Why is this?

'Select First Cell in Target file sheet
Worksheets("Template").Range("A2").Select

'Choose Used Range of target sheet
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

Thanks