Automatically sort a workbook on opening and delete blank rows.

Hi All,

I am trying to educate myself in the wonders of Excel (2003) and an struggling somewhat.
I have a small project loosely based on a shops stock control and have attached a copy of the workbook - if it helps.

The user normally only has access to columns B, C and E due to the others being locked and the sheet protected.
I would like the sheet to be automatically sorted based on column E each time the workbook is opened.

I then need to automatically delete a row once an item in column B has been removed (made blank)

Any help would be greatly appreciated

Drew

Hi,

Use this code.

Sub try()

Range("E6:E230").Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("E6"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet1").Sort
.SetRange Range("B6:F230")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Dim i, LastRow
LastRow = Range("B" & Rows.Count).End(xlUp).Row
For i = LastRow To 1 Step -1
If Cells(i, "B").Value = "" Then
Cells(i, "B").EntireRow.Delete
End If
Next


End Sub



Babu.S

Hi there,

Thanks so much for your help. I have a problem though and it is my fault.

When I first asked the question I made the mistake of asking for the rows to be deleted.

What I meant to say was " I need the rows with items still in them moved up to fill the blank rows where items have been removed.
My logic being that if the rows are deleted eventually the 230 rows that have various formatting and formulas in them would eventually
be deleted also.

Once again thank you if you can help.

By the way I'm trying my best to understand your macros

Andrew

Hi Andrew,

I have modified the file and attached the same.
open the file and run the macro. let me know if its ok.

Babu.S