How to navigate a filtered range programmatically?

navigation , vba , filtered range India
  • 10 years ago

    suppose i have a column of blank and non blank values mixed. If i want to validate the non blank cells i will apply the auto filter for that column by giving the criteria as non blanks and then i will navigate through that filtered range by using the keyboard but how can we navigate that filtered range programmatically? There must be some way.

  • 10 years ago

    namespace Jitender.OfficeTools.Excel { using System; using System.IO; using Excel = Microsoft.Office.Interop.Excel;

    class DemoExcelAutoFiltering {
        [STAThread]
        static void Main(string[] args) {
            DirectoryInfo di = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE11\1033");
            ExcelFileReport efr = new ExcelFileReport(di);
            efr.Generate();
        }
    }
    
    public class ExcelFileReport {
        private object _missing;
        private Excel.Workbook _book;
        Excel.Worksheet _sheet;
        Excel.Range _rng;
        int _row;
        private DirectoryInfo _di;
        ExcelHelper _eh = new ExcelHelper();
    
        public ExcelFileReport(DirectoryInfo di) {
            _di = di;
            _missing = System.Reflection.Missing.Value;
            _row = 4;
        }
    
        public void DocumentDirectory(DirectoryInfo di) {
            foreach (DirectoryInfo d in di.GetDirectories()) {
                DocumentDirectory(d);
            }
    
            foreach (FileInfo f in di.GetFiles()) {
                _row++;
                _rng = (Excel.Range)_sheet.Cells[_row, "A"];
                _rng.Value2 = di.Name;
                _rng = (Excel.Range)_sheet.Cells[_row, "B"];
                _rng.Value2 = f.FullName;
                _rng = (Excel.Range)_sheet.Cells[_row, "C"];
                _rng.Value2 = f.Name;
                _rng = (Excel.Range)_sheet.Cells[_row, "D"];
                _rng.Value2 = f.Length;
                _rng = (Excel.Range)_sheet.Cells[_row, "E"];
                _rng.Value2 = f.Extension;
                _rng = (Excel.Range)_sheet.Cells[_row, "F"];
                _rng.Value2 = f.LastWriteTime.ToLongDateString();
            }
        }
    
        public void Generate(){
            string caption = "File Analysis Results";
            string heading1 = "File Analysis Report for Folder " + _di.FullName;
            _book = _eh.Create(caption, heading1);
            _sheet = ((Excel.Worksheet)_book.ActiveSheet);
            WriteTableHeader();
            DocumentDirectory(_di);
            SetAutoFilter();
            _eh.Close();
        }
    
        private void SetAutoFilter(){
            string lastrow = "F" + _row.ToString();
            _rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
            _rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
            _rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
        }
    
        public void WriteTableHeader(){
            _rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
            _rng.Font.Bold = true;
            _rng.EntireRow.Font.Bold = true;
    
            _rng = (Excel.Range)_sheet.Cells[_row, "A"];
            _rng.Value2 = "Parent Directory";
            _rng = (Excel.Range)_sheet.Cells[_row, "B"];
            _rng.Value2 = "Full Path";
            _rng = (Excel.Range)_sheet.Cells[_row, "C"];
            _rng.Value2 = "File Name";
            _rng = (Excel.Range)_sheet.Cells[_row, "D"];
            _rng.Value2 = "Size";
            _rng = (Excel.Range)_sheet.Cells[_row, "E"];
            _rng.Value2 = "Type";
            _rng = (Excel.Range)_sheet.Cells[_row, "F"];
            _rng.Value2 = "Last Modified";
    
            _sheet.Columns.ColumnWidth = 30;
        }
    }
    
    class ExcelHelper {
        private Excel.Application _excelApplication;
    
        public ExcelHelper() {
            _excelApplication = new Excel.Application();
        }
    
        public Excel.Workbook Create(string caption, string heading1) {
            try {
                _excelApplication.Caption = caption;
                _excelApplication.ScreenUpdating = false;
                _excelApplication.Visible = false;
    
                Excel.Workbook book = _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
                Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;
    
                Excel.Range r = (Excel.Range)sheet.Cells[1, "A"];
                r.Value2 = heading1;
                r.EntireRow.Font.Bold = true;
    
                return book;
    
            }
            catch (Exception ex) {
                throw (ex);
            }
        }
    
        public void Close() {
            _excelApplication.ScreenUpdating = true;
            _excelApplication.Visible = true;
            _excelApplication.DisplayAlerts = true;
    
            if (_excelApplication != null) {
                _excelApplication.Quit();
                _excelApplication = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
    

    }

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“The greatest performance improvement of all is when a system goes from not-working to working.” - John Ousterhout