Ranges using offset
 

Let's say I have the following to delete the contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named range -
say "last_column" which is the number 67. How would I
change the above code to referenc the number in the range
vs manually edititing the code? Looking for same help
with row (say "last_row" = 100)

Ranges using offset
 

Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count, (Columns.Count - .Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen" skrev i meddelandet
...
Let's say I have the following to delete the contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named range -
say "last_column" which is the number 67. How would I
change the above code to referenc the number in the range
vs manually edititing the code? Looking for same help
with row (say "last_row" = 100)

Ranges using offset
 

Do you mean firts_row and last_row?

Sheets("Main").Select
Application.GoTo Reference:="C" & first_row & ":C25" & last_row
Selection.Delete Shift:=xlToLeft


--

HTH

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

"Fatir Zelen" wrote in message
...
Let's say I have the following to delete the contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named range -
say "last_column" which is the number 67. How would I
change the above code to referenc the number in the range
vs manually edititing the code? Looking for same help
with row (say "last_row" = 100)

Ranges using offset
 

Got a compile error at the .Column)) part - didn't use
option explicit or sub since inside existing code but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen" skrev
i meddelandet
...
Let's say I have the following to delete the contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named
range -
say "last_column" which is the number 67. How would I
change the above code to referenc the number in the
range
vs manually edititing the code? Looking for same help
with row (say "last_row" = 100)



.

Ranges using offset
 

No typo, and I'm not Bob.

It looks like a line break has been inserted somewhere along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before you enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen" skrev i meddelandet
...
Got a compile error at the .Column)) part - didn't use
option explicit or sub since inside existing code but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen" skrev
i meddelandet
...
Let's say I have the following to delete the contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named
range -
say "last_column" which is the number 67. How would I
change the above code to referenc the number in the
range
vs manually edititing the code? Looking for same help
with row (say "last_row" = 100)



.

Ranges using offset
 

Sorry the name error.

Here is what I have and it still doesn't work, even on its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
.Column)).Delete (this is actually in the live above)
End With
End Sub

End_Col is the named range with a value of 66 so it would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen" skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't use
option explicit or sub since inside existing code but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"
skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named
range -
say "last_column" which is the number 67. How would
I
change the above code to referenc the number in the
range
vs manually edititing the code? Looking for same
help
with row (say "last_row" = 100)



.


.

Ranges using offset
 

Firts you have a hyphen instead of a continuation caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen" wrote in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even on its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
.Column)).Delete (this is actually in the live above)
End With
End Sub

End_Col is the named range with a value of 66 so it would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen" skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't use
option explicit or sub since inside existing code but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"
skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a named
range -
say "last_column" which is the number 67. How would
I
change the above code to referenc the number in the
range
vs manually edititing the code? Looking for same
help
with row (say "last_row" = 100)



.


.

Ranges using offset
 

I tried using the code below. I get a messsage saying

Compile error:
Invalid qualifier

the part .Count

in .... (Columns.Count _

gets highlighed.

Any thoughts?


-----Original Message-----
Firts you have a hyphen instead of a continuation
caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or
C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen" wrote
in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even on
its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete (this is actually in the live above)
End With
End Sub

End_Col is the named range with a value of 66 so it
would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before
you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen"
skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't
use
option explicit or sub since inside existing code
but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"
skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a
named
range -
say "last_column" which is the number 67. How
would
I
change the above code to referenc the number in
the
range
vs manually edititing the code? Looking for same
help
with row (say "last_row" = 100)



.


.



.

Ranges using offset
 

I think that was me mis-reading it, it really should be hyphen.

Give this a whirl, although it may not work if you really mean named ranges

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count - _
.Column)).Delete (this is actually in the live above)
End With
End Sub



--

HTH

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

"Fatir Zelen" wrote in message
...
I tried using the code below. I get a messsage saying

Compile error:
Invalid qualifier

the part .Count

in .... (Columns.Count _

gets highlighed.

Any thoughts?


-----Original Message-----
Firts you have a hyphen instead of a continuation
caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or
C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen" wrote
in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even on
its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete (this is actually in the live above)
End With
End Sub

End_Col is the named range with a value of 66 so it
would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before
you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen"
skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't
use
option explicit or sub since inside existing code
but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"
skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a
named
range -
say "last_column" which is the number 67. How
would
I
change the above code to referenc the number in
the
range
vs manually edititing the code? Looking for same
help
with row (say "last_row" = 100)



.


.



.

Ranges using offset
 

Still having trouble.

Now getting run time error '1004'
Method 'Range" of object'_Global'failed

Not that up to speed on VBA but my guess is that its not
picking up the value of the range "End_Col"

If you have any other ideas let me know. Thanks for all
your help. /Fatir



-----Original Message-----
I think that was me mis-reading it, it really should be
hyphen.

Give this a whirl, although it may not work if you really
mean named ranges

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
_
.Column)).Delete (this is actually in the
live above)
End With
End Sub



--

HTH

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

"Fatir Zelen" wrote
in message
...
I tried using the code below. I get a messsage saying

Compile error:
Invalid qualifier

the part .Count

in .... (Columns.Count _

gets highlighed.

Any thoughts?


-----Original Message-----
Firts you have a hyphen instead of a continuation
caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or
C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen"
wrote
in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even
on
its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete (this is actually in the live
above)
End With
End Sub

End_Col is the named range with a value of 66 so it
would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted
somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before
you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen"
skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't
use
option explicit or sub since inside existing code
but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"

skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a
named
range -
say "last_column" which is the number 67. How
would
I
change the above code to referenc the number in
the
range
vs manually edititing the code? Looking for
same
help
with row (say "last_row" = 100)



.


.



.



.

Ranges using offset
 

OK Fatir, let's try again,

Here is the code I posted in my first answer, I have just added column labels so
it will be clear that the Sub only has three lines of code.

Make sure that there is a column named "lastColumn" and the Sub will delete
everything to the right of column "lastColumn".

'-----
Option Explicit
Sub abc()
Line1: With Range("lastColumn")
Line2: .Offset(0, 1).Resize(Rows.Count, (Columns.Count - .Column)).Delete
Line3: End With
End Sub
'-----

Regards
Anders Silven

"Fatir Zelen" skrev i meddelandet
...
Still having trouble.

Now getting run time error '1004'
Method 'Range" of object'_Global'failed

Not that up to speed on VBA but my guess is that its not
picking up the value of the range "End_Col"

If you have any other ideas let me know. Thanks for all
your help. /Fatir



-----Original Message-----
I think that was me mis-reading it, it really should be
hyphen.

Give this a whirl, although it may not work if you really
mean named ranges

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
_
.Column)).Delete (this is actually in the
live above)
End With
End Sub



--

HTH

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

"Fatir Zelen" wrote
in message
...
I tried using the code below. I get a messsage saying

Compile error:
Invalid qualifier

the part .Count

in .... (Columns.Count _

gets highlighed.

Any thoughts?


-----Original Message-----
Firts you have a hyphen instead of a continuation
caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or
C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen"
wrote
in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even
on
its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete (this is actually in the live
above)
End With
End Sub

End_Col is the named range with a value of 66 so it
would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted
somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before
you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen"
skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't
use
option explicit or sub since inside existing code
but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"

skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a
named
range -
say "last_column" which is the number 67. How
would
I
change the above code to referenc the number in
the
range
vs manually edititing the code? Looking for
same
help
with row (say "last_row" = 100)



.


.



.



.

Ranges using offset
 

If End_Col is really a named range then try

Worksheets("Main").Select
With Range("C" & Range("End_col"))
.Offset(0, 1).Resize(Rows.Count, (Columns.Count - .Column)).Delete
End With


--

HTH

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

"Fatir Zelen" wrote in message
...
Still having trouble.

Now getting run time error '1004'
Method 'Range" of object'_Global'failed

Not that up to speed on VBA but my guess is that its not
picking up the value of the range "End_Col"

If you have any other ideas let me know. Thanks for all
your help. /Fatir



-----Original Message-----
I think that was me mis-reading it, it really should be
hyphen.

Give this a whirl, although it may not work if you really
mean named ranges

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count, (Columns.Count -
_
.Column)).Delete (this is actually in the
live above)
End With
End Sub



--

HTH

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

"Fatir Zelen" wrote
in message
...
I tried using the code below. I get a messsage saying

Compile error:
Invalid qualifier

the part .Count

in .... (Columns.Count _

gets highlighed.

Any thoughts?


-----Original Message-----
Firts you have a hyphen instead of a continuation
caharacter. An, if end_col
is 66 it is not a named range as a range -s C6 or
C6:C100. You might try
this

Sub remove_blanks()
Sheets("Main").Select
With Range("C" &End_Col)
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count _
.Column)).Delete (this is actually in the live above)
End With
End Sub


--

HTH

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

"Fatir Zelen"
wrote
in message
...
Sorry the name error.

Here is what I have and it still doesn't work, even
on
its
own.

Sub remove_blanks()
Sheets("Main").Select
With Range("End_Col")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete (this is actually in the live
above)
End With
End Sub

End_Col is the named range with a value of 66 so it
would
delete columns 67-256


Any sugggestions??

-----Original Message-----
No typo, and I'm not Bob.

It looks like a line break has been inserted
somewhere
along the line.
".Column)).Delete" belongs to the line above it.

Try the macro by itself to make sure it works before
you
enter it into your
existing code

Regards
Anders Silven


"Fatir Zelen"
skrev
i meddelandet
...
Got a compile error at the .Column)) part - didn't
use
option explicit or sub since inside existing code
but I
dont think that should matter.

Was that a typo Bob?
-----Original Message-----
Fatir,

Try this

'-----
Option Explicit

Sub abc()
With Range("lastColumn")
.Offset(0, 1).Resize(Rows.Count,
(Columns.Count -
.Column)).Delete
End With
End Sub
'-----

HTH
Anders Silven

"Fatir Zelen"

skrev
i meddelandet
...
Let's say I have the following to delete the
contents of
Column 68 to 256.

Sheets("Main").Select
Application.GoTo Reference:="C68:C256"
Selection.Delete Shift:=xlToLeft

but instead I want the reference to be from a
named
range -
say "last_column" which is the number 67. How
would
I
change the above code to referenc the number in
the
range
vs manually edititing the code? Looking for
same
help
with row (say "last_row" = 100)



.


.



.



.