Amend code - Important - Please

[keilah]

hi i have the following vba code.

I need the code to stop inserting row in the ActualData worksheet and just delete the item i have told it to delete from the Eligible Portfolios sheet.

I also need to keep row 8 columns c to ay fixed "header information" - all other rows below if deleted - with the macro function to move up one, keeping the data compact.

Also need to auto function filter removed......

here is the code:

Public Sub DeletionCriteria()
Dim mpCriteria As String
Do
mpCriteria = InputBox("PLEASE DELETE ANY OLD PORTFOLIO DATA - ")
If mpCriteria <> "" Then
Call DeleteData(Worksheets("EligiblePortfolios").Columns("B:B"), mpCriteria, True)
Call DeleteData(Worksheets("ActualData").Columns("E:E"), mpCriteria)
End If
Loop While mpCriteria <> ""
End Sub

Private Sub DeleteData(pzData As Range, pzCriteria, Optional blnHeader As Boolean = False)

If pzData.Parent.AutoFilterMode Then
pzData.AutoFilter
End If

With Intersect(pzData, pzData.Parent.UsedRange)
If Not blnHeader Then
.Parent.Rows(1).Insert
.Cells(1, 1).Value = ""
.AutoFilter Field:=1, Criteria1:=pzCriteria
If Not Intersect(.Parent.Range("14:65536"), .SpecialCells(xlCellTypeVisible)) Is Nothing Then
Intersect(.Parent.Range("14:65536"), .SpecialCells(xlCellTypeVisible)).EntireRow.Delete
End If
.AutoFilter
Else
.AutoFilter Field:=1, Criteria1:=pzCriteria
.Offset(14, 0).Resize(.Rows.Count - 14).SpecialCells(xlCellTypeVisible).EntireRow.Delete
.AutoFilter
End If
End With

End Sub

thanks.

[Bob Phillips]

I need the code to stop inserting row in the ActualData worksheet and just delete the item i have told it to delete from the Eligible Portfolios sheet.

Insert which row?

I also need to keep row 8 columns c to ay fixed "header information" - all other rows below if deleted - with the macro function to move up one, keeping the data compact.

What does that mean?

Also need to auto function filter removed......

And what does that mean?

Can't you post the workbook, guessing your data is tough.

[keilah]

Hi Xld

point 1. Each time the user tells the macro to delete a entry from the Eligible Portfolios worksheet, the same entry is also deleted from the ActualData worksheet. this works fine.
However, when you go to the actualdata worksheet to check that the entry has been removed. The original data the was placed in

row 8 columns C:AY - moves to row 9 same columns range.

Point2. Row 8 column C:AY has information that tells the user what information in each column relates to. So delete all rows below row 8....keeping row 8 information displayed all the time.

Point3. Ignore this point silly point raised by me...

thanks for the feedback....await your reply.

once again thanks

[Bob Phillips]

As far as I can see, it starts at row 14 so I don't see what row 8 has to do with it.

As I said, without a workbook it is very difficult to envisage the data, so I for one withdraw until I see one.