Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi! is there a way in VBA to remove all empty rows in a worksheet 'Sheet1'? Cheers Juergen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Juergen,
Try: '============= Public Sub Tester() On Error Resume Next Columns(1).SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "juergenkemeter" <juergenkemeter.21fyyy_1136934901.6862@excelforu m-nospam.com wrote in message news:juergenkemeter.21fyyy_1136934901.6862@excelfo rum-nospam.com... Hi! is there a way in VBA to remove all empty rows in a worksheet 'Sheet1'? Cheers Juergen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Juergen,
Or, better and less telegrammatic: '============= Public Sub Tester2() Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE On Error Resume Next SH.Columns(1).SpecialCells(xlBlanks).EntireRow.Del ete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "Norman Jones" wrote in message ... Hi Juergen, Try: '============= Public Sub Tester() On Error Resume Next Columns(1).SpecialCells(xlBlanks).EntireRow.Delete On Error GoTo 0 End Sub '<<============= --- Regards, Norman "juergenkemeter" <juergenkemeter.21fyyy_1136934901.6862@excelforu m-nospam.com wrote in message news:juergenkemeter.21fyyy_1136934901.6862@excelfo rum-nospam.com... Hi! is there a way in VBA to remove all empty rows in a worksheet 'Sheet1'? Cheers Juergen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi, I tried Code: -------------------- Public Sub Tester2() Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE On Error Resume Next SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete On Error GoTo 0 End Sub -------------------- to delete all rows which have no value in range B to M. Somehow it does nothing special... I enclosed an example workbook. cheers Juergen +-------------------------------------------------------------------+ |Filename: DeleteEmptyRow.zip | |Download: http://www.excelforum.com/attachment.php?postid=4206 | +-------------------------------------------------------------------+ -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Norman's code actually looked at one column to determine if the row is empty.
If you can't pick out a column that is always filled in whenever something in that row is filled in, you could use something like: Option Explicit Public Sub Tester2a() Dim WB As Workbook Dim SH As Worksheet Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row FirstRow = 1 For iRow = LastRow To FirstRow Step -1 If Application.CountA(.Rows(iRow)) = 0 Then .Rows(iRow).Delete End If Next iRow End With End Sub If you really only wanted to check columns B:M (ignoring A and N:IV): Option Explicit Public Sub Tester2a() Dim WB As Workbook Dim SH As Worksheet Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row FirstRow = 1 For iRow = LastRow To FirstRow Step -1 If Application.CountA _ (.Range(.Cells(iRow, "B"), .Cells(iRow, "M"))) = 0 Then .Rows(iRow).Delete End If Next iRow End With End Sub juergenkemeter wrote: Hi, I tried Code: -------------------- Public Sub Tester2() Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE On Error Resume Next SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete On Error GoTo 0 End Sub -------------------- to delete all rows which have no value in range B to M. Somehow it does nothing special... I enclosed an example workbook. cheers Juergen +-------------------------------------------------------------------+ |Filename: DeleteEmptyRow.zip | |Download: http://www.excelforum.com/attachment.php?postid=4206 | +-------------------------------------------------------------------+ -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Juergen,
You appear to have moved the goal posts: your original question was to remove all empty rows. However, try: '============= Public Sub Tester3() Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE On Error Resume Next SH.Columns("B").SpecialCells(xlBlanks).EntireRow.D elete On Error GoTo 0 End Sub '<<============= If, however, column B cannot be used to define empty rows, post back with more detail. --- Regards, Norman "juergenkemeter" <juergenkemeter.21g1ip_1136938205.9638@excelforu m-nospam.com wrote in message news:juergenkemeter.21g1ip_1136938205.9638@excelfo rum-nospam.com... Hi, I tried Code: -------------------- Public Sub Tester2() Dim WB As Workbook Dim SH As Worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE On Error Resume Next SH.Columns("B:M").SpecialCells(xlBlanks).EntireRow .Delete On Error GoTo 0 End Sub -------------------- to delete all rows which have no value in range B to M. Somehow it does nothing special... I enclosed an example workbook. cheers Juergen +-------------------------------------------------------------------+ |Filename: DeleteEmptyRow.zip | |Download: http://www.excelforum.com/attachment.php?postid=4206 | +-------------------------------------------------------------------+ -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Juergen,
If, however, column B cannot be used to define empty rows, post back with more detail. Given, however, Dave's intervening and comprehensive response, that should be unnecessary. --- Regards, Norman |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Missed it by a minute!
Norman Jones wrote: Hi Juergen, If, however, column B cannot be used to define empty rows, post back with more detail. Given, however, Dave's intervening and comprehensive response, that should be unnecessary. --- Regards, Norman -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi! Dave, your first code sample does the necessary. Could you shortly explain how your code works? Thanks to you both for your help! Juergen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Option Explicit
Public Sub Tester2a() Dim WB As Workbook Dim SH As Worksheet Dim LastRow As Long Dim FirstRow As Long Dim iRow As Long 'set it up to point at the correct workbook/worksheet Set WB = ActiveWorkbook '<<==== CHANGE Set SH = WB.Sheets("Sheet1") '<<==== CHANGE With SH 'lastrow is the same as the row number for the cell you go 'to when you hit ctrl-end manually. LastRow = .Cells.SpecialCells(xlCellTypeLastCell).Row 'just a stopping point. 'if you had a bunch of headers (some empty rows), you 'could ignore them by changing this to a larger number FirstRow = 1 'start at the lastrow and go up the rows (step -1 is up) For iRow = LastRow To FirstRow Step -1 'if you see anything (formulas or values in that row) 'then =counta() will be 0 If Application.CountA(.Rows(iRow)) = 0 Then 'but if it no cells are filled in, then 'delete that row .Rows(iRow).Delete End If Next iRow End With End Sub juergenkemeter wrote: Hi! Dave, your first code sample does the necessary. Could you shortly explain how your code works? Thanks to you both for your help! Juergen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi, can I specify a certain range condition for deleting a row, for Dave's code? e.g. If there are no values in Range(Columns A : J), then delete row -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Juergen.
can I specify a certain range condition for deleting a row, for Dave's code? e.g. If there are no values in Range(Columns A : J), then delete row Try changing: If Application.CountA(.Rows(iRow)) = 0 Then to: If Application.CountA(Cells(iRow, "A").Resize(1, 10)) = 0 Then --- Regards, Norman "juergenkemeter" <juergenkemeter.21k7in_1137132602.7149@excelforu m-nospam.com wrote in message news:juergenkemeter.21k7in_1137132602.7149@excelfo rum-nospam.com... Hi, can I specify a certain range condition for deleting a row, for Dave's code? e.g. If there are no values in Range(Columns A : J), then delete row -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi! I altered the line, but nothing happens with the lines. I enclosed an example file - principally, I want to delete all rows in the Sheet, except rows 1,3,4,5,7,8. All these rows contain values in column range A to J. Cheers Jürgen +-------------------------------------------------------------------+ |Filename: deleteEmptyRows.zip | |Download: http://www.excelforum.com/attachment.php?postid=4221 | +-------------------------------------------------------------------+ -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Jürgen,
I altered the line, but nothing happens with the lines. I enclosed an example file - principally, I want to delete all rows in the Sheet, except rows 1,3,4,5,7,8. All these rows contain values in column range A to J. I have not opened your attachment but I have reviewed my test book. In my tests any rows on Sheet1 of the active workbook are deleted if, and only if, columns A:J are blank. This is as expected and in accordance with the code's logic. Can I suggest, therefore, that you retry the suggested code, ensuring that the expected worksheet is active. Should you wish me to send you my test book, please provide an appropriately disguised email address. --- Regards, Norman |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Norman, thanks, it works. I used the line If Application.CountA(Cells(iRow, "A").Resize(1, 9)) = 0 Then than means 1,9 and not 1,10 (10 would mean that it also looks into column K). Thanks for your help. Cheers Jürgen -- juergenkemeter ------------------------------------------------------------------------ juergenkemeter's Profile: http://www.excelforum.com/member.php...o&userid=25248 View this thread: http://www.excelforum.com/showthread...hreadid=499951 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - remove all empty rows in a worksheet
Hi Jurgen,
Hi Norman, thanks, it works. I used the line If Application.CountA(Cells(iRow, "A").Resize(1, 9)) = 0 Then than means 1,9 and not 1,10 (10 would mean that it also looks into column K). No, That is not correct. From the immediate window: ?Range("A1").Resize(1,10).Address $A$1:$J$1 --- Regards, Norman |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to remove empty rows? | Excel Discussion (Misc queries) | |||
How do I remove empty Rows | Excel Discussion (Misc queries) | |||
Remove empty rows in excel? | Excel Discussion (Misc queries) | |||
remove all blank or empty rows | Excel Programming | |||
Remove empty rows | Excel Programming |