By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,551 Members | 2,710 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,551 IT Pros & Developers. It's quick & easy.

VB.NET Excel Special Cells and AutoFilter problems

P: 2
Hi

I'm trying to format an Excel file from within VB.NET 2008 (Framework 2.0).

I'm not very familiar with Excel but have taken existing working code from an old VB6 program and am "tweaking" it to get it to work in VB.NET.

I have most of it working with the exception of the following lines:

rng = ws.Range(xclApp.Intersect(ws.Rows(7).EntireRow, ws.UsedRange.Columns(1)), ws.UsedRange.SpecialCells(XlCellType.xlCellTypeLas tCell))
rng.Value = rng.Value

'Works correctly up until above line (no cells found for the following)

'For Each cel In rng.Offset(1).SpecialCells(XlCellType.xlCellTypeAl lFormatConditions, Type.Missing)
' If Trim(cel) = "" Then cel = cel.Offset(-1).Value
'Next cel

'rng.Worksheet.AutoFilterMode = False
'rng.AutoFilter(Field:=12, Criteria1:="<>*Total*", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="=*Grand*")
'rng.Offset(1).SpecialCells(XlCellType.xlCellTypeV isible).EntireRow.Delete(Shift:=XlDirection.xlUp)
'rng.Value = rng.Value
''rng.AutoFilter()
'rng.Worksheet.AutoFilterMode = False

'--------------------------------------------------------------------

rng.Font.Bold = False

Hope someone can help me get this going....
Aug 31 '08 #1
Share this Question
Share on Google+
1 Reply


P: 2
Sorry I didn't explain the problem with the following portion:

'rng.Worksheet.AutoFilterMode = False
'rng.AutoFilter(Field:=12, Criteria1:="<>*Total*", Operator:=XlAutoFilterOperator.xlOr, Criteria2:="=*Grand*")
'rng.Offset(1).SpecialCells(XlCellType.xlCellTypeV isible).EntireRow.Delete(Shift:=XlDirection.xlUp)
'rng.Value = rng.Value
''rng.AutoFilter()
'rng.Worksheet.AutoFilterMode = False

This is supposed to filter through and only delete certain lines based on the criteria given.

It deletes those lines but I also lose all the data in all of the fields with the exception of field 12.
Aug 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.