I would like to resize an existing range without having to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so that the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it resized
my range so that it became A to H, ie 8 columns. Not very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui

Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count - 1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jacqui" wrote in message
...
I would like to resize an existing range without having to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so that the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it resized
my range so that it became A to H, ie 8 columns. Not very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui

Hi Bob

Thank you very much for your reply , didn't think to use
offset!
Code works great except that my source data originates
from a CSV file so when I paste it to the target file I
need to do a paste special, CSV (I tried it first
manually). Sorry forgot to mention this previously.

How would I incorporate a paste special, csv into the
following?

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With

Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
'wsNew.Columns("A:IV").AutoFit
wsNew.Name = sRange & vChanArr(n)

Would really appreciate your help.

Many thanks
Jacqui

-----Original Message-----
Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jacqui" wrote in
message
...
I would like to resize an existing range without having
to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so that
the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it
resized
my range so that it became A to H, ie 8 columns. Not
very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui




.

There is no pastespecial CSV. If the file was properly parsed in the
original, a simple paste should suffice.

--
Regards,
Tom Ogilvy

"jacqui" wrote in message
...

Hi Bob

Thank you very much for your reply , didn't think to use
offset!
Code works great except that my source data originates
from a CSV file so when I paste it to the target file I
need to do a paste special, CSV (I tried it first
manually). Sorry forgot to mention this previously.

How would I incorporate a paste special, csv into the
following?

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With

Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
'wsNew.Columns("A:IV").AutoFit
wsNew.Name = sRange & vChanArr(n)

Would really appreciate your help.

Many thanks
Jacqui

-----Original Message-----
Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"jacqui" wrote in
message
...
I would like to resize an existing range without having
to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so that
the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it
resized
my range so that it became A to H, ie 8 columns. Not
very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui




.

Tom

Thank you for your reply. Yeah I take your point, my
Import sub should format it correctly in the first place.
I've tried to rectify this by adding in a PasteSpecial
xlvalues in place of Paste. However, I'm getting an
Object Defined error . Would you mind having a look at my
code below because I'm wondering if the change should be
in the OpenText bit instead. Sorry it's a bit lengthy but
at least you can see the full sub.
Many thanks
Jacqui

Sub import_data()

Dim Openfile
With Application
.DisplayAlerts = False
.StatusBar = " Importing ERS Source File"
.ScreenUpdating = False
End With

ChDrive "l:\"
ChDir ALT_LOC & "\source data\"

Application.ScreenUpdating = True
Openfile = Application.GetOpenFilename("Text
Files (*.txt),*.txt")
If Openfile < False Then
Application.ScreenUpdating = False
Worksheets("Data").Select
Range("a1").Select

If Not IsEmpty(Range
("a1").Value) Then
Set OldRegion =
ActiveCell.CurrentRegion
Set NewRegion = Range
(OldRegion.Cells(1, 1), OldRegion.Cells
(OldRegion.Rows.Count, 108))
NewRegion.ClearContents
End If
Range("a1").Select

Workbooks.OpenText FileName:=Openfile,
_
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _

Textqualifier:=xlTextQualifierDoubleQuote, _
Comma:=True
Else
MsgBox "You have either not selected a
file or selected Cancel - the process will be terminated."
GoTo Reset_Screen
End If
Application.ScreenUpdating = False
ActiveWindow.Caption = "Data File"
Range("a1").Select
Selection.CurrentRegion.Copy
Windows("ERS-Data Management File").Activate
Worksheets("Data").Select
Range("a1").Select

IT'S GOING WRONG ON THE FOLLOWING LINE
ActiveSheet.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("a1").Select
Selection.CurrentRegion.Name = "DatArea"

Worksheets("Menu").Range("e16").Value = Openfile
Worksheets("Menu").Range("e17").Value = Mid
(Worksheets("Data").Range("a2").Value, 16, 9) & " " & _

Mid(Worksheets("Data").Range
("a1").Value, 9, 4)
Worksheets("Menu").Range("e18").Value = Mid
(Worksheets("Data").Range("a3").Value, 1, 2)
Worksheets("Menu").Range("e19").Value = Mid
(Worksheets("Data").Range("a3").Value, 4, 4)

Windows("Data File").Activate
ActiveWindow.Close savechanges:=False

ActiveSheet.Range("A1:A3").Select
Selection.EntireRow.Delete

Sub_Totals

Reset_Screen:
Windows("ERS-Data Management File").Activate
Worksheets("Menu").Select
Range("a1").Select
With Application
.DisplayAlerts = True
.StatusBar = False
.ScreenUpdating = True
End With
End Sub



-----Original Message-----
There is no pastespecial CSV. If the file was properly
parsed in the
original, a simple paste should suffice.

--
Regards,
Tom Ogilvy

"jacqui" wrote in
message
...

Hi Bob

Thank you very much for your reply , didn't think to use
offset!
Code works great except that my source data originates
from a CSV file so when I paste it to the target file I
need to do a paste special, CSV (I tried it first
manually). Sorry forgot to mention this previously.

How would I incorporate a paste special, csv into the
following?

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With

Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
'wsNew.Columns("A:IV").AutoFit
wsNew.Name = sRange & vChanArr(n)

Would really appreciate your help.

Many thanks
Jacqui

-----Original Message-----
Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"jacqui" wrote in
message
...
I would like to resize an existing range without
having
to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so
that
the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it
resized
my range so that it became A to H, ie 8 columns. Not
very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui




.



.

Pastespecial has two different forms. One is with a worksheet object, the
other is with a range. They each have different arguments. You have mixed
the two. (used a worksheet object and range arguments)

Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Should work.

--
Regards,
Tom Ogilvy


"Jacqui" wrote in message
...
Tom

Thank you for your reply. Yeah I take your point, my
Import sub should format it correctly in the first place.
I've tried to rectify this by adding in a PasteSpecial
xlvalues in place of Paste. However, I'm getting an
Object Defined error . Would you mind having a look at my
code below because I'm wondering if the change should be
in the OpenText bit instead. Sorry it's a bit lengthy but
at least you can see the full sub.
Many thanks
Jacqui

Sub import_data()

Dim Openfile
With Application
.DisplayAlerts = False
.StatusBar = " Importing ERS Source File"
.ScreenUpdating = False
End With

ChDrive "l:\"
ChDir ALT_LOC & "\source data\"

Application.ScreenUpdating = True
Openfile = Application.GetOpenFilename("Text
Files (*.txt),*.txt")
If Openfile < False Then
Application.ScreenUpdating = False
Worksheets("Data").Select
Range("a1").Select

If Not IsEmpty(Range
("a1").Value) Then
Set OldRegion =
ActiveCell.CurrentRegion
Set NewRegion = Range
(OldRegion.Cells(1, 1), OldRegion.Cells
(OldRegion.Rows.Count, 108))
NewRegion.ClearContents
End If
Range("a1").Select

Workbooks.OpenText FileName:=Openfile,
_
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlDelimited, _

Textqualifier:=xlTextQualifierDoubleQuote, _
Comma:=True
Else
MsgBox "You have either not selected a
file or selected Cancel - the process will be terminated."
GoTo Reset_Screen
End If
Application.ScreenUpdating = False
ActiveWindow.Caption = "Data File"
Range("a1").Select
Selection.CurrentRegion.Copy
Windows("ERS-Data Management File").Activate
Worksheets("Data").Select
Range("a1").Select

IT'S GOING WRONG ON THE FOLLOWING LINE
ActiveSheet.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("a1").Select
Selection.CurrentRegion.Name = "DatArea"

Worksheets("Menu").Range("e16").Value = Openfile
Worksheets("Menu").Range("e17").Value = Mid
(Worksheets("Data").Range("a2").Value, 16, 9) & " " & _

Mid(Worksheets("Data").Range
("a1").Value, 9, 4)
Worksheets("Menu").Range("e18").Value = Mid
(Worksheets("Data").Range("a3").Value, 1, 2)
Worksheets("Menu").Range("e19").Value = Mid
(Worksheets("Data").Range("a3").Value, 4, 4)

Windows("Data File").Activate
ActiveWindow.Close savechanges:=False

ActiveSheet.Range("A1:A3").Select
Selection.EntireRow.Delete

Sub_Totals

Reset_Screen:
Windows("ERS-Data Management File").Activate
Worksheets("Menu").Select
Range("a1").Select
With Application
.DisplayAlerts = True
.StatusBar = False
.ScreenUpdating = True
End With
End Sub



-----Original Message-----
There is no pastespecial CSV. If the file was properly
parsed in the
original, a simple paste should suffice.

--
Regards,
Tom Ogilvy

"jacqui" wrote in
message
...

Hi Bob

Thank you very much for your reply , didn't think to use
offset!
Code works great except that my source data originates
from a CSV file so when I paste it to the target file I
need to do a paste special, CSV (I tried it first
manually). Sorry forgot to mention this previously.

How would I incorporate a paste special, csv into the
following?

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With

Range(sRange & "data").Copy
Destination:=wsNew.Range("B10")
'wsNew.Columns("A:IV").AutoFit
wsNew.Name = sRange & vChanArr(n)

Would really appreciate your help.

Many thanks
Jacqui

-----Original Message-----
Morning Jacqui,

Give this a try

With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count -
1, .Columns.Count - 7).Name =
(sRange & "data")
End With

--

HTH

Bob Phillips
... looking out across Poole Harbour to the
Purbecks
(remove nothere from the email address if mailing
direct)

"jacqui" wrote in
message
...
I would like to resize an existing range without
having
to
hard code in the new column reference if poss. The
original size of my range is A1:DG4155. The range is
called sRange and is originally set using the
CurrentRegion property. I'd like to resize it so
that
the
first column is H instead of A and the first row is 2
instead of 1.

The code I'm using is...

firstcol = 8

With Range(sRange)
.Resize(, firstcol).Name = (sRange & "data")
End With

However, this produced the wrong result in that it
resized
my range so that it became A to H, ie 8 columns. Not
very
clever.

Can anyone kindly help with some syntax?
Many thanks
Jacqui




.



.