Roger,

As per your email ... The Data should be copied from column "E" onwards to
the next blank column.eg. E5="Manchester E6= 0610 and if F5 and F6 are blank
then it should be copied to these cells. Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if populated.

I was thinking of running this process prior to deleting columns if row 8&9
are blank [for which you have already supplied code].

Hope this helps.

Thanks,

"Roger Whitehead" wrote:

Rows 6 + 7 appear to be project creep!

I wish to delete columns from column "E" onwards which have no value
on
rows
8 and 9. Also I would like to place a "thick box border" around rows
6
to
10
up to the and including the last column with data.

We don't usually like attachments, but can you attach a *CSV* version of a
portion of your sheet? Please make sure you state clearly the starting cell
of the CSV (I'd prefer it to be A1, but if it isn't please advise).

Regards
Roger

"manfareed" wrote in message
...
Hi Roger,

This works but causes another problem.When deleting the columns it deletes
some of the headings which are in rows 6+7. Would it be possible to copy
the
row headings from rows 6+7 to the next "blank" column to the right i.e. it
contains data in rows 7 to 9 but has no heading detail in rows 6+7.

It follows that if there are 2 blanks columns after the row heading then
it
should be copied to the next to these blank cells. Eg if "Birmingham" row
6
and "060300" is row 7is in column E and F&G are blank then it should be
copied to F&G.

Thanks,

Manir


"Roger Whitehead" wrote:

Sorry - that's my comment at

find LastCell

The word wrap has broken the comment line & tuned it into code. Comment
that
line out & try again.

R

"manfareed" wrote in message
...
Hi Roger,

I get an error- "sub or function not defined".

Thanks,






"Roger Whitehead" wrote:

Hi manfareed, try the following. This assumes Excel to 2003, and that
at
least the last row of the sheet is empty

Sub deleteCols()
Rows(1).Insert
Set myrange = Range("E1:IV1")
myrange.FormulaR1C1 = "=IF(AND(LEN(R9C)=0,LEN(R10C)=0),1,"""")"
myrange.Value = myrange.Value
myrange.SpecialCells(xlCellTypeConstants,
xlNumbers).EntireColumn.Delete

'Loop for now, but could Select A1 and save WB, then use special cells
to
find LastCell
For c = 255 To 1 Step -1
If Application.WorksheetFunction.CountA(Range(Cells(2 , c),
Cells(65536,
c))) < 0 Then
lastcol = c
Exit For
End If
Next

Rows(1).Delete
Range(Cells(6, 5), Cells(10, lastcol)).BorderAround , xlThick,
xlColorIndexAutomatic
End Sub


---
HTH
Roger
Shaftesbury (UK)


"manfareed" wrote in message
...
Hi

I wish to delete columns from column "E" onwards which have no value
on
rows
8 and 9. Also I would like to place a "thick box border" around rows
6
to
10
up to the and including the last column with data.

Thanks