Range Resize property

  • Thread starter Thread starter jacqui
  • Start date Start date
J

jacqui

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)
 
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
 
There is no pastespecial CSV. If the file was properly parsed in the
original, a simple paste should suffice.
 
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
 
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.
 
Tom,

Thank you. I'd just about got there with the XL help but
your explanation was much clearer! Just one final thing.
My source data is now pasting to the "data" sheet as
values, however when I step through the original code as
below I'm still missing a bit. If I do a manual
copy/pastespecial, I'm offered the PasteSpecial dialog box
(unlike before where I had to select a type of file which
means we must have remedied the .CSV bit). By doing it
manually it proves I can get the data across in the right
format but how would I incorporate a pastespecial values
into my Copy Destination line as below. Sorry but I'm
stuck again.
Hope you can help.
Many thanks
Jacqui


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.Name = sRange & vChanArr(n)
 
With Range(sRange)
.Offset(1, 7).Resize(.Rows.Count - _
1, .Columns.Count - 7) _
.Name = (sRange & "data")
End With

Range(sRange & "data").Copy

wsNew.Range("B10").Pastespecial Paste:=xlValues

wsNew.Name = sRange & vChanArr(n)
 
Back
Top