#How to delete certain rows in excel vba how to
#How to delete certain rows in excel vba code

The next two macros are examples of what I call 'automatic code,' because they execute automatically, if a certain condition is met. MsgBox Err.Description & ' Procedure FindHide' 'If rSearch was indeed = Nothing, there was 'Now we start a loop that finds all other 'You can also define another range than column B. 'With ActiveSheet' or 'With Worksheets(nb. 'If you want to search the entire worksheet, you write 'In this example we search column B only. 'Hides all rows with a certain value in column B. Note that '?' and '*' can be used as wildcards or 'jokers'. We ask the user what to search for, and a row is hidden if it has the seach value in column B. The following example uses Excel's search function in a given range - here column B, but that can easily be changed. MsgBox Err.Description & ' Procedure HideEmpt圜olumns' If rTest.Row = Rows.Count And Len(rTest.Value) = 0 Then 'Go to the first cell with content below. 'rCell is set = the cell lCol to the right of cell A1. 'We now loop through the columns from left to right. 'Loops the first 26 columns and hide them, if they are empty.

#How to delete certain rows in excel vba how to

However before we start playing hide and seek, I want to show you how to make the hidden rows and columns visible again! The first example shows how to hide empty columns, and the next example shows how to use the search function and hide rows, if a certain value exists in a given range. The other approach is to run an 'ordinary' macro that checks if certain conditions are met. The WorkSheet_Change procedure is activated every time something is changed in the worksheet.

#How to delete certain rows in excel vba code

You can do it 'automatically' by putting code in the worksheet's code sheet. To unzip, right-click and select whatever Windows suggests to unzip the archive.īasically you can use two different methods. You can also download a zip-compressed spreadsheet with the examples. To test the code, highlight it with the mouse, copy (CTRL+C) and paste (CTRL+V) into a VBA module.

On this page are examples on how to do it. Hide or show rows and columns in Excel with VBAĪ frequently asked question is how to hide rows and/or columns in Excel using a VBA macro.