Hi Guys,

I need to find the last column in a set of data.
Once the last column is found, I need to delete all data EXCEPT for the
last column (say column az) and except for the first Four columns (a to
d) ie all columns between d and bz needs to be deleted. Column d will
always be the same,but column bz is variable (ie could be column ay or
ax or anything.

Thanks

D



*** Sent via Developersdex http://www.developersdex.com ***

Hi Darin

Try this for the activesheet

Sub test()
Range(Cells(1, 5), Cells(1, Lastcol(ActiveSheet) - 1)).EntireColumn.Delete
End Sub

Function Lastcol(sh As Worksheet)
On Error Resume Next
Lastcol = sh.Cells.Find(What:="*", _
After:=sh.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
On Error GoTo 0
End Function


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Darin Kramer" wrote in message
...
Hi Guys,

I need to find the last column in a set of data.
Once the last column is found, I need to delete all data EXCEPT for the
last column (say column az) and except for the first Four columns (a to
d) ie all columns between d and bz needs to be deleted. Column d will
always be the same,but column bz is variable (ie could be column ay or
ax or anything.

Thanks

D



*** Sent via Developersdex http://www.developersdex.com ***

Works perfectly!!!! Thanks so very much!


*** Sent via Developersdex http://www.developersdex.com ***

Sub DeleteExcessData()
Dim rng As Range
Set rng = ActiveSheet.UsedRange
If Intersect(ActiveSheet.Columns("A:D"), rng) Is Nothing Then
MsgBox "Cant"
ElseIf rng.Columns.Count <= 5 Then
MsgBox "too narrow"
Else
rng.Columns(5).Resize(, rng.Columns.Count - 5).EntireColumn.Delete
End If

End Sub




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Darin Kramer wrote :

Hi Guys,

I need to find the last column in a set of data.
Once the last column is found, I need to delete all data EXCEPT for
the last column (say column az) and except for the first Four columns
(a to d) ie all columns between d and bz needs to be deleted. Column
d will always be the same,but column bz is variable (ie could be
column ay or ax or anything.

Thanks

D



*** Sent via Developersdex http://www.developersdex.com ***