You’re probably already familiar with the InputBox function as a tool for soliciting data from the user. Set selectedRng = Application.InputBox(“Range”,, selectedRng.Address, Type:=8) Some of the commands probably need a bit of explanation. SelectedRng.Rows(iForCount).EntireRow.Delete If (selectedRng.Rows(iForCount)) = 0 Then 'WorksheetFunction.CountA counts the number of cells that are NOT empty.
'Count of rows in selected range used as For stop value.
Set selectedRng = Application.InputBox("Range",, selectedRng.Address, Type:=8) 'Type:=8 argument specifies a Range object input value must be a range. 'Choose appropriate delete statement in For loop. When it finds one, the loop deletes the row and shifts the remaining rows up. After declaring and setting a few variables, a For loop checks every row in the selected range for blank rows. SEE: 3 handy Excel data entry shortcuts (free TechRepublic PDF) The macroįirst, let’s review the code shown in Listing A. If I knew the originator, I’d thank them and offer credit where due. This macro, in one form another has been around for a long time. There might be data off screen that you don’t see. NOTE: Be careful when deleting entire rows. cls file from the download into your workbook. Don’t try to copy the code from this web page the Visual Basic Editor (VBE) will complain about phantom characters it can’t interpret. For your convenience, you can download the demonstration. You can’t run macros in the browser edition.
Delete certain rows in excel windows 10#
I’m using Office 365 (Excel 2016 desktop) on a Windows 10 64-bit system. In this article, I’ll show you a macro that does the job for you. Most of Excel’s selection-based features interpret a blank row as the end of a data range consequently, those features won’t work as expected if you leave blank rows in your data set. Regardless of how you acquire blank rows in data sets, it’s best to remove them. But there’s a sixth way: You can use a macro. The article 5 ways to delete blank rows in Excel demonstrates several methods for deleting blank rows manually.
Delete certain rows in excel how to#
How to create a fun Fly In effect in PowerPoint Get lifetime access to Microsoft Office 2021 for just $50 There are many ways to delete empty rows in an Excel sheet, but this macro might be the easiest of all. An Excel macro that deletes blank rows in a specified range
0 kommentar(er)
