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" <(E-Mail Removed)> wrote in message
news:0906AEED-4F2C-477F-ACB8-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:0906AEED-4F2C-477F-ACB8-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
news:871B91F1-4A54-4155-BC75-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:0906AEED-4F2C-477F-ACB8-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
> news:871B91F1-4A54-4155-BC75-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:0906AEED-4F2C-477F-ACB8-(E-Mail Removed)...
> >> > 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" <(E-Mail Removed)> wrote in message
news:F8026703-5745-4390-9583-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:871B91F1-4A54-4155-BC75-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> wrote in message
>> >> news:0906AEED-4F2C-477F-ACB8-(E-Mail Removed)...
>> >> > 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
>> >>
>> >>
>> >>
>>
>>
>>