You see a lot of old Save As code that does not specify the File Format parameter.In Excel versions before Excel 2007, code without this parameter will not cause too many problems because Excel will use the current File Format of the existing file and the default File Format for new files is a (xls) in 97-2003 because there are no other Excel file formats before Excel 2007.When you turn on the filter in an Excel worksheet list, or if you create a named Excel table, each cell in the heading row automatically shows a drop down arrow.The arrow button shows a filter icon, if you have filtered that column, and if you point to the arrow button it shows the filter criteria.There are code examples below, for hiding arrows in a List Auto Filter, and a worksheet Auto Filter.There are more Auto Filter VBA examples on my Contextures website: If you want to leave one or more arrows visible, but hide the others, you can use VBA.Here cell value criteria is the condition which you want to check the cells to delete rows. Please follow the below steps to execute the VBA code to delete rows based on cell value from Excel worksheets.Step 1: Open any Excel workbook Step 2: Press Alt F11 – This will open the VBA Editor Step 3: Insert a code module from then insert menu Step 4: Copy the above code and paste in the code module which have inserted in the above step Step 5: Enter some sample data in first column from row 1 to 20 Step 6: Now press F5 to execute the code Now you can observe that the rows are deleted from worksheet if the cell value is 10.
We are using the Delete method of the Rows object of worksheet. Delete method will delete the Entire rows from the Excel spreadsheet.
For example, in Excel 2007-2016, this will fail if the Active Workbook is not an xlsm file' 52 = xl Open XMLWorkbook Macro Enabled = xlsm (with macro's in 2007-2016) These are the main file formats in Excel 2007-2016, Note: In Excel for the Mac the values are 151 = xl Open XMLWorkbook (without macro's in 2007-2016, xlsx)52 = xl Open XMLWorkbook Macro Enabled (with or without macro's in 2007-2016, xlsm) 50 = xl Excel12 (Excel Binary Workbook in 2007-2016 with or without macro's, xlsb)56 = xl Excel8 (97-2003 format in Excel 2007-2016, xls)Note: I always use the File Format numbers instead of the defined constants in my code so that it will compile OK when I copy the code into an Excel 97-2003 workbook (For example, Excel 97-2003 won't know what the xl Open XMLWorkbook Macro Enabled constant is).
Below are two basic code examples to copy the Active Sheet to a new Workbook and save it in a format that matches the file extension of the parent workbook.
This tutorial explains how to use Excel VBA used Range Property to find the last used row and column number, and reset used Range.
You may also want to read: Excel VBA custom Function last row and last column Excel delete blank rows and blank cells before import into Access Excel VBA Used Range is a worksheet Property, it returns the area Range bounded by first used cell and last used cell.