Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing blank lines in a workbook
I am trying to use code that was listed in a previous newsgroup posting to
remove blank lines on a worksheet. Is there a way to set this up as a macro that would work for all worksheets in the workbook? I am not very knowledgeable with VB language. Below is the posting I have followed so far. It works for 1 worksheet, but not for the entire workbook. Thanks for any help. I have assumed that if the cell in column A is empty then this identifies a blank row. If that is correct then right click the sheet tab, view code and paste this in:- Sub surface() With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For x = LastRow To 1 Step -1 Cells(x, 1).Select If Cells(x, 1).Value = "" Then Selection.EntireRow.Delete End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Removing blank lines in a workbook
Sue,
Try something like the following: Sub surface() Dim WS As Worksheet Dim LastRow As Long Dim X As Long For Each WS In ActiveWorkbook.Worksheets With WS LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For X = LastRow To 1 Step -1 If .Cells(X, 1).Value = "" Then .Rows(X).Delete End If Next X End With Next WS End Sub A few comments: You will find your life much better if you ALWAYS declare your variables using the Dim statement. You can enforce this practice by including Option Explicit as the very first line in the module, before and outside of any Sub or Function procedure. This can be automatically set by checking the "Required Variable Declaration" in the "Editor" tab of the "Options" dialog from the "Tools" menu in VBA. See also http://www.cpearson.com/Excel/variables.htm. Next, you (almost) never need to Select or Activate anything in VBA. Doing so only causes the code to run slower and is, in my opinion, more difficult to maintain. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting www.cpearson.com (email on the web site) "Sue" wrote in message ... I am trying to use code that was listed in a previous newsgroup posting to remove blank lines on a worksheet. Is there a way to set this up as a macro that would work for all worksheets in the workbook? I am not very knowledgeable with VB language. Below is the posting I have followed so far. It works for 1 worksheet, but not for the entire workbook. Thanks for any help. I have assumed that if the cell in column A is empty then this identifies a blank row. If that is correct then right click the sheet tab, view code and paste this in:- Sub surface() With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row End With For x = LastRow To 1 Step -1 Cells(x, 1).Select If Cells(x, 1).Value = "" Then Selection.EntireRow.Delete End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to delete Blank lines in excel workbook? | Excel Discussion (Misc queries) | |||
How do I print lines in a blank workbook? | New Users to Excel | |||
Removing zero value lines | Excel Worksheet Functions | |||
Copying and pasting a worksheet to a blank and removing blank rows | Excel Programming | |||
how to automatically insert blank lines in between non-blank lines | Excel Programming |