Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activate sh
I utilize forms for running an excel spreadsheet.
I have a macro which deletes empty rows (of a specified range). I have it so the macro works over several sheets. The macro works fine, however it needs to activate and perform its actions in each sheet in turn to select the rows and delete them. I now activate the macro from a userform, and was wondering if there was a way to execute the code such that the macro doesnt require each sheet to be activated (ie visible) to work. It is quite distracting having the sheets scrolling through in the background. I tried to activate the code with the sheets hidden but the code doesnt work when I do this. i have attached the code I use for information. It seems somewhat ungainly and I wondered if there was a better way to do this without having to have the sheets visible. I eventually would like all the sheets hidden so the user doesnt need to access them. Public Sub DeleteRows() Dim Rng As Range, Rng1 As Range Set Rng = Range("A81:E140") On Error Resume Next Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub Can anyone help or advise me? Thasnkyou, Roger |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activatesh
Hi Roger
You did not specify which sheets you wanted the blanks in Col B deleted. The following works on all sheets. Take care Marcus Option Explicit Sub DelBlankColB() Dim lw As Integer Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets lw = ws.Range("B" & Rows.Count).End(xlUp).Row ws.Range("B1:B" & lw).SpecialCells (xlCellTypeBlanks).EntireRow.Delete Next ws End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activate sh
The following code will delete all rows in all worksheets where the
cells A:D on each row are empty. If one or more cells in A:D on a row have a value, that row is not deleted. Change the "A:D" to whatever columns you want to test. Sub AAA() Dim WS As Worksheet Dim R As Range Dim N As Long Dim LastRow As Long Const CHECK_RANGE As String = "A:D" '<<<<<< CHANGE For Each WS In ThisWorkbook.Worksheets With WS LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For N = LastRow To 1 Step -1 If Application.CountA(.Range(CHECK_RANGE).Rows(N)) = 0 Then .Rows(N).Delete End If Next N End With Next WS End Sub Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 15 Dec 2009 13:52:01 -0800, Roger on Excel wrote: I utilize forms for running an excel spreadsheet. I have a macro which deletes empty rows (of a specified range). I have it so the macro works over several sheets. The macro works fine, however it needs to activate and perform its actions in each sheet in turn to select the rows and delete them. I now activate the macro from a userform, and was wondering if there was a way to execute the code such that the macro doesnt require each sheet to be activated (ie visible) to work. It is quite distracting having the sheets scrolling through in the background. I tried to activate the code with the sheets hidden but the code doesnt work when I do this. i have attached the code I use for information. It seems somewhat ungainly and I wondered if there was a better way to do this without having to have the sheets visible. I eventually would like all the sheets hidden so the user doesnt need to access them. Public Sub DeleteRows() Dim Rng As Range, Rng1 As Range Set Rng = Range("A81:E140") On Error Resume Next Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub Can anyone help or advise me? Thasnkyou, Roger |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activate sh
Roger,
In general, using objects does not require you to activate a sheet. This is because the object knows what it is (i.e. its identity) and where it belongs. For example, "Dim Rng As Range" creates Rng as an object with a Range identity. Rng then takes on all of the Range class attributes. Additionally, the Range object has a general hierarchy of Workbook and Worksheet. As a result, Rng.Parent is the same thing as referring to the Worksheet object where Rng resides. Furthermore, Rng.Parent.Parent is the same thing as referring to the Workbook object where Rng resides. Another small, often overlooked, issue is that of "qualifying" your objects. If you don't specify the Workbook/Worksheet hierarchy, then the ActiveWorkbook/ActiveWorksheet gets appended to your Range object. So, "Set Rng = Range("A81:E140")" refers to ActiveWorkbook.ActiveWorksheet.Range("A81:E140"). If, for example, I wanted to refer to Sheet2 and not the ActiveWorksheet (which we'll say is Sheet1), then I would have to do something like "Set Rng = Worksheets("Sheet2").Range("A81:E140")". Again, this appends ActiveWorkbook to the object because no specific Workbook is specified. If you do perform selections (which is almost completely unnecessary), deletions, etc., then you can help the "speed" of things by doing at least two things (1) set the calculation mode to Manual, and (2) turn off screen updating. The code for this is below. I think that screen updating will solve your problem. I hope this helps. Best, Matthew Herbert Sub Test() Dim xlCalc As XlCalculation With Application 'save off the current calculation mode xlCalc = .Calculation 'set calculation to manual .Calculation = xlCalculationManual 'turn off screen updating .ScreenUpdating = False End With 'reset the calculation mode Application.Calculation = xlCalc 'in this example there is no need to reset ScreenUpdating ' because it returns to true each time the procedure finishes End Sub "Roger on Excel" wrote: I utilize forms for running an excel spreadsheet. I have a macro which deletes empty rows (of a specified range). I have it so the macro works over several sheets. The macro works fine, however it needs to activate and perform its actions in each sheet in turn to select the rows and delete them. I now activate the macro from a userform, and was wondering if there was a way to execute the code such that the macro doesnt require each sheet to be activated (ie visible) to work. It is quite distracting having the sheets scrolling through in the background. I tried to activate the code with the sheets hidden but the code doesnt work when I do this. i have attached the code I use for information. It seems somewhat ungainly and I wondered if there was a better way to do this without having to have the sheets visible. I eventually would like all the sheets hidden so the user doesnt need to access them. Public Sub DeleteRows() Dim Rng As Range, Rng1 As Range Set Rng = Range("A81:E140") On Error Resume Next Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub Can anyone help or advise me? Thasnkyou, Roger |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activat
Dear Matthew,
Thankyou for this - the code works nicely to deactivate all the scrolling and flickering in the backgroand and its much quicker too All the best, Roger "Matthew Herbert" wrote: Roger, In general, using objects does not require you to activate a sheet. This is because the object knows what it is (i.e. its identity) and where it belongs. For example, "Dim Rng As Range" creates Rng as an object with a Range identity. Rng then takes on all of the Range class attributes. Additionally, the Range object has a general hierarchy of Workbook and Worksheet. As a result, Rng.Parent is the same thing as referring to the Worksheet object where Rng resides. Furthermore, Rng.Parent.Parent is the same thing as referring to the Workbook object where Rng resides. Another small, often overlooked, issue is that of "qualifying" your objects. If you don't specify the Workbook/Worksheet hierarchy, then the ActiveWorkbook/ActiveWorksheet gets appended to your Range object. So, "Set Rng = Range("A81:E140")" refers to ActiveWorkbook.ActiveWorksheet.Range("A81:E140"). If, for example, I wanted to refer to Sheet2 and not the ActiveWorksheet (which we'll say is Sheet1), then I would have to do something like "Set Rng = Worksheets("Sheet2").Range("A81:E140")". Again, this appends ActiveWorkbook to the object because no specific Workbook is specified. If you do perform selections (which is almost completely unnecessary), deletions, etc., then you can help the "speed" of things by doing at least two things (1) set the calculation mode to Manual, and (2) turn off screen updating. The code for this is below. I think that screen updating will solve your problem. I hope this helps. Best, Matthew Herbert Sub Test() Dim xlCalc As XlCalculation With Application 'save off the current calculation mode xlCalc = .Calculation 'set calculation to manual .Calculation = xlCalculationManual 'turn off screen updating .ScreenUpdating = False End With 'reset the calculation mode Application.Calculation = xlCalc 'in this example there is no need to reset ScreenUpdating ' because it returns to true each time the procedure finishes End Sub "Roger on Excel" wrote: I utilize forms for running an excel spreadsheet. I have a macro which deletes empty rows (of a specified range). I have it so the macro works over several sheets. The macro works fine, however it needs to activate and perform its actions in each sheet in turn to select the rows and delete them. I now activate the macro from a userform, and was wondering if there was a way to execute the code such that the macro doesnt require each sheet to be activated (ie visible) to work. It is quite distracting having the sheets scrolling through in the background. I tried to activate the code with the sheets hidden but the code doesnt work when I do this. i have attached the code I use for information. It seems somewhat ungainly and I wondered if there was a better way to do this without having to have the sheets visible. I eventually would like all the sheets hidden so the user doesnt need to access them. Public Sub DeleteRows() Dim Rng As Range, Rng1 As Range Set Rng = Range("A81:E140") On Error Resume Next Set Rng1 = Intersect(Rng, Columns("B:B").SpecialCells(xlBlanks)) On Error GoTo 0 If Not Rng1 Is Nothing Then Rng1.EntireRow.Delete End Sub Can anyone help or advise me? Thasnkyou, Roger |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activat
Dear Marcus,
Thanks for replying, The cells are B81:B140 in the following named sheets St1,St2,St3,St4,St5.......St10 Would you be able to modify the code to cycle through these sheets for me? Roger "marcus" wrote: Hi Roger You did not specify which sheets you wanted the blanks in Col B deleted. The following works on all sheets. Take care Marcus Option Explicit Sub DelBlankColB() Dim lw As Integer Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets lw = ws.Range("B" & Rows.Count).End(xlUp).Row ws.Range("B1:B" & lw).SpecialCells (xlCellTypeBlanks).EntireRow.Delete Next ws End Sub . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activat
Hi Roger
Here is the procedure which will work only on those sheets which start with 'St' Adjust to suit. If you have sheets you don't want it to work on which do start with St let us know. Take care Marcus Option Explicit Option Compare Text Sub DelBlankColB() Dim lw As Integer Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, 2) = "St" Then lw = ws.Range("B" & Rows.Count).End(xlUp).Row ws.Range("B1:B" & lw).SpecialCells (xlCellTypeBlanks).EntireRow.Delete End If Next ws End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting Empty Rows using VBA code without the need to activat
Many Thanks Marcus - Have a great week
Roger "marcus" wrote: Hi Roger Here is the procedure which will work only on those sheets which start with 'St' Adjust to suit. If you have sheets you don't want it to work on which do start with St let us know. Take care Marcus Option Explicit Option Compare Text Sub DelBlankColB() Dim lw As Integer Dim ws As Worksheet Application.ScreenUpdating = False For Each ws In ThisWorkbook.Worksheets If Left(ws.Name, 2) = "St" Then lw = ws.Range("B" & Rows.Count).End(xlUp).Row ws.Range("B1:B" & lw).SpecialCells (xlCellTypeBlanks).EntireRow.Delete End If Next ws End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting empty rows | New Users to Excel | |||
Deleting Empty Rows | Excel Programming | |||
Deleting empty rows | Excel Programming | |||
Deleting non empty rows | Excel Programming | |||
Deleting empty rows | Excel Programming |