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

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

Test this on a copy of your sheet.

Sub cleansheet()
Dim j As Long, i As Long
j = 0
For i = 256 To 5 Step -1
If Application.CountA(Cells(8, i).Resize(2, 1)) = 0 Then
Columns(i).EntireColumn.Delete
Else
j = j + 1
End If
Next
ActiveSheet.UsedRange
With Cells(6, 1).Resize(5, 4 + j)
.BorderAround Weight:=xlThick
End With

--
Regards,
Tom Ogilvy


End Sub"manfareed" wrote:

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

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

Hi Tom,

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



"Tom Ogilvy" wrote:

Test this on a copy of your sheet.

Sub cleansheet()
Dim j As Long, i As Long
j = 0
For i = 256 To 5 Step -1
If Application.CountA(Cells(8, i).Resize(2, 1)) = 0 Then
Columns(i).EntireColumn.Delete
Else
j = j + 1
End If
Next
ActiveSheet.UsedRange
With Cells(6, 1).Resize(5, 4 + j)
.BorderAround Weight:=xlThick
End With

--
Regards,
Tom Ogilvy


End Sub"manfareed" wrote:

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

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

Sorry, I can't visualize what you want - but your working with Roger anyway,
so no use me getting in the way.

--
Regards,
Tom Ogilvy


"manfareed" wrote:

Hi Tom,

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



"Tom Ogilvy" wrote:

Test this on a copy of your sheet.

Sub cleansheet()
Dim j As Long, i As Long
j = 0
For i = 256 To 5 Step -1
If Application.CountA(Cells(8, i).Resize(2, 1)) = 0 Then
Columns(i).EntireColumn.Delete
Else
j = j + 1
End If
Next
ActiveSheet.UsedRange
With Cells(6, 1).Resize(5, 4 + j)
.BorderAround Weight:=xlThick
End With

--
Regards,
Tom Ogilvy


End Sub"manfareed" wrote:

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

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

Tom,

No problem. I work with whoever can help me.
If you can please do.

Thanks,

Manir

"Tom Ogilvy" wrote:

Sorry, I can't visualize what you want - but your working with Roger anyway,
so no use me getting in the way.

--
Regards,
Tom Ogilvy


"manfareed" wrote:

Hi Tom,

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



"Tom Ogilvy" wrote:

Test this on a copy of your sheet.

Sub cleansheet()
Dim j As Long, i As Long
j = 0
For i = 256 To 5 Step -1
If Application.CountA(Cells(8, i).Resize(2, 1)) = 0 Then
Columns(i).EntireColumn.Delete
Else
j = j + 1
End If
Next
ActiveSheet.UsedRange
With Cells(6, 1).Resize(5, 4 + j)
.BorderAround Weight:=xlThick
End With

--
Regards,
Tom Ogilvy


End Sub"manfareed" wrote:

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

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

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

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Hi Roger,

Sorry for the confusion. Not all columns are blanks. It varies each time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg. Milton
Keynes. If it does then "Milton Keynes " data would be copied to the next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we delete
8&9 if blank it becomes difficult for me to identify which branch the values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Manir, I think (hope!) I understand more now...

'Code Start--------------------------------
For c = 255 To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) < 0 Then
lastcol = c
Exit For
End If
Next

'<Copy contents of rows 6&7-----------
For c = 5 To (lastcol - 1) Step 1
If Cells(6, c).Value < "" And Cells(6, c + 1).Value = "" Then
Cells(6, c + 1).Value = Cells(6, c).Value
End If
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If
Next c
'</Copy contents of rows 6&7-----------

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
'Watch out for word wrap-
If Application.WorksheetFunction.CountA(Range(Cells(2 , c), Cells(65536,
c))) < 0 Then
lastcol = c
Exit For
End If
Next

Rows(1).Delete
'Watch out for wprd wrap-
Range(Cells(6, 5), Cells(10, lastcol)).BorderAround , xlThick,
xlColorIndexAutomatic
'Code End--------------------------------


--
---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Roger Whitehead" wrote in message
...
Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we
delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

How would I attach a CSV ?

"Roger Whitehead" wrote:

Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Roger,

You've done it !!! Excellent

Only thing is it works for only row 6 and not 5 ...

Thanks,

Manir

"Roger Whitehead" wrote:

Manir, I think (hope!) I understand more now...

'Code Start--------------------------------
For c = 255 To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) < 0 Then
lastcol = c
Exit For
End If
Next

'<Copy contents of rows 6&7-----------
For c = 5 To (lastcol - 1) Step 1
If Cells(6, c).Value < "" And Cells(6, c + 1).Value = "" Then
Cells(6, c + 1).Value = Cells(6, c).Value
End If
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If
Next c
'</Copy contents of rows 6&7-----------

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
'Watch out for word wrap-
If Application.WorksheetFunction.CountA(Range(Cells(2 , c), Cells(65536,
c))) < 0 Then
lastcol = c
Exit For
End If
Next

Rows(1).Delete
'Watch out for wprd wrap-
Range(Cells(6, 5), Cells(10, lastcol)).BorderAround , xlThick,
xlColorIndexAutomatic
'Code End--------------------------------


--
---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Roger Whitehead" wrote in message
...
Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we
delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving holes
behind... Ok, so we could work in reverse, but that may potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

My mistake, just change each 7 for a 5 in -
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If

Cheers!
Roger
Shaftesbury (UK)


"manfareed" wrote in message
...
Roger,

You've done it !!! Excellent

Only thing is it works for only row 6 and not 5 ...

Thanks,

Manir

"Roger Whitehead" wrote:

Manir, I think (hope!) I understand more now...

'Code Start--------------------------------
For c = 255 To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) < 0 Then
lastcol = c
Exit For
End If
Next

'<Copy contents of rows 6&7-----------
For c = 5 To (lastcol - 1) Step 1
If Cells(6, c).Value < "" And Cells(6, c + 1).Value = "" Then
Cells(6, c + 1).Value = Cells(6, c).Value
End If
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If
Next c
'</Copy contents of rows 6&7-----------

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
'Watch out for word wrap-
If Application.WorksheetFunction.CountA(Range(Cells(2 , c),
Cells(65536,
c))) < 0 Then
lastcol = c
Exit For
End If
Next

Rows(1).Delete
'Watch out for wprd wrap-
Range(Cells(6, 5), Cells(10, lastcol)).BorderAround , xlThick,
xlColorIndexAutomatic
'Code End--------------------------------


--
---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Roger Whitehead" wrote in message
...
Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we
delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for
eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to
the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving
holes
behind... Ok, so we could work in reverse, but that may
potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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

Roger,

Many Thanks ... Great Stuff.

Regards,

Manir

"Roger Whitehead" wrote:

My mistake, just change each 7 for a 5 in -
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If

Cheers!
Roger
Shaftesbury (UK)


"manfareed" wrote in message
...
Roger,

You've done it !!! Excellent

Only thing is it works for only row 6 and not 5 ...

Thanks,

Manir

"Roger Whitehead" wrote:

Manir, I think (hope!) I understand more now...

'Code Start--------------------------------
For c = 255 To 1 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) < 0 Then
lastcol = c
Exit For
End If
Next

'<Copy contents of rows 6&7-----------
For c = 5 To (lastcol - 1) Step 1
If Cells(6, c).Value < "" And Cells(6, c + 1).Value = "" Then
Cells(6, c + 1).Value = Cells(6, c).Value
End If
If Cells(7, c).Value < "" And Cells(7, c + 1).Value = "" Then
Cells(7, c + 1).Value = Cells(7, c).Value
End If
Next c
'</Copy contents of rows 6&7-----------

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
'Watch out for word wrap-
If Application.WorksheetFunction.CountA(Range(Cells(2 , c),
Cells(65536,
c))) < 0 Then
lastcol = c
Exit For
End If
Next

Rows(1).Delete
'Watch out for wprd wrap-
Range(Cells(6, 5), Cells(10, lastcol)).BorderAround , xlThick,
xlColorIndexAutomatic
'Code End--------------------------------


--
---
HTH
Roger
Shaftesbury (UK)
(Excel 2003, Win XP/SP2)

"Roger Whitehead" wrote in message
...
Without seeing what you've got, this is very difficult. If you want to
attach a CSV go ahead - change confidential details first.

The follwing code will strip empty columns from E:IV first, which may
help...

Watch out for the Word Wrap!

Roger

'Code----------------------------------------------
'<Clear empty columns first---------------
For c = 255 To 5 Step -1
If Application.WorksheetFunction.CountA(Columns(c)) = 0 Then
lastcol = c
Exit For
End If
Next
'</Clear empty columns first---------------

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 code------------------------------------------







"manfareed" wrote in message
...
Hi,

Not always the case. I want data in 5&6 because they will be branch
identifiers i.e. branch name and code. 8&9 are for data only. If we
delete
8&9 if blank it becomes difficult for me to identify which branch the
values
belong to.

Thanks,

Manir

"Roger Whitehead" wrote:

If rows 5&6 are blank, will rows 8&9 also be blank?
If rows 8&9 are blank, will rows 5&6 also be blank?

If there' a consistency, it would be helpful...

R


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

Sorry for the confusion. Not all columns are blanks. It varies each
time I
run a report.

Column G rows 5&6 could be blank or it could say contain data for
eg.
Milton
Keynes. If it does then "Milton Keynes " data would be copied to
the
next
blank column.

I hope this helps.

Thanks,

Manir
"Roger Whitehead" wrote:

Hi Manir,

Now you've lost me:
..... Similarly G5="Milton Keynes ,
G6="06120" then Milton Keynes should be copied say to G if
populated.

Milton Keynes copies to G (G5, presumably?)
If populated? Of if NOT populated?

If data is continually copied to the right, it will be leaving
holes
behind... Ok, so we could work in reverse, but that may
potentially
leave
columns E to (say) Z empty.

Any chance of that CSV copy, and a broader view of your objective?

Regards
Roger


"manfareed" wrote in message
...
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: