Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Hi my name is Nile.
I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Hi,
It seems you have identified part of the problem yourself. It does not work on multiple selections. The code you put in does not delete the right row because i is started at 1 and the row starts at 8: Range("A8:A58,D8:K58").Select I think you need to Col A, then D, E F G H I J and K one at a time Selection.Rows(i).EntireRow.Delete (i is not the ActiveRow, if there is an activerow) "Nile Gilmanov" wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
I think I'd loop through the rows, but check each column that I wanted:
Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Thanks!
I will use your ideas and see how I can get this figured out. All the best, Nile "Nile Gilmanov" wrote in message ... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Hey Dave,
what if I used a COUNTIF worksheet function instead of COUNTA (something like COUNTIF(RANGE,""), do you think it's a good idea? Nile "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
And by the way... Any Ideas how I can ignore the cells that have formulas
returning "", I think both counta and countif consider tham as cells that have values. The rows that I must delete have references to some worksheet with values which sometimes don't exist, in that case "" shows. Please help me. Nile |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
AWESOME this works very well... xcept i had to modify it just a smidge!
I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Be careful. It looks to me like you have a small bug in your code.
This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Dave,
Thanks a lot man, you really saved me a lot of time :))). LOL it's ironical cuz yesterday night I was working on this code of mine and it wasn't doing the right thing I did feel like it had a bug, it would ignore a value in the range that I gave and delete the row, cuz there was no value in the column that I was supposed to be skipping :)))). may God keep blessing you with knowledge and wisdom and understanding :), Nile "Dave Peterson" wrote in message ... Be careful. It looks to me like you have a small bug in your code. This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
Bah..... Dave I think that Worksheet function does not like intersected
rows, it returns the following error: Unable to get the CountBlank property of the WorksheetFunction class (Run-time error '1004':).. .and it does work with "RANGE", "RANGE" kind of notation. It's too bad I was hoping it's all done :)))). Thanks anyway! :). all the best, Nile "Dave Peterson" wrote in message ... Be careful. It looks to me like you have a small bug in your code. This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
You're correct about =countblank(). (I didn't try it.)
But you could either just use worksheetfunction.countblank() twice -- once for each set of columns or you could loop through each area (nicer if you ever expand those columns to more non-contiguous areas): Option Explicit Sub testme() Dim myRng As Range Dim i As Long Dim myRng2 As Range Dim myBlankCtr As Long Dim myArea As Range With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) myBlankCtr = 0 For Each myArea In myRng2.Areas myBlankCtr = myBlankCtr + WorksheetFunction.CountBlank(myArea) Next myArea If myBlankCtr = myRng2.Cells.Count Then myRng(i).Delete End If Next i End With End Sub Nile Gilmanov wrote: Bah..... Dave I think that Worksheet function does not like intersected rows, it returns the following error: Unable to get the CountBlank property of the WorksheetFunction class (Run-time error '1004':).. .and it does work with "RANGE", "RANGE" kind of notation. It's too bad I was hoping it's all done :)))). Thanks anyway! :). all the best, Nile "Dave Peterson" wrote in message ... Be careful. It looks to me like you have a small bug in your code. This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
hah thank you Dave, now i am completely happy! :)))
All the best, Nile PS I wonder why I could not find anything like this on the usenet archives, seems very practical to me :). "Dave Peterson" wrote in message ... You're correct about =countblank(). (I didn't try it.) But you could either just use worksheetfunction.countblank() twice -- once for each set of columns or you could loop through each area (nicer if you ever expand those columns to more non-contiguous areas): Option Explicit Sub testme() Dim myRng As Range Dim i As Long Dim myRng2 As Range Dim myBlankCtr As Long Dim myArea As Range With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) myBlankCtr = 0 For Each myArea In myRng2.Areas myBlankCtr = myBlankCtr + WorksheetFunction.CountBlank(myArea) Next myArea If myBlankCtr = myRng2.Cells.Count Then myRng(i).Delete End If Next i End With End Sub Nile Gilmanov wrote: Bah..... Dave I think that Worksheet function does not like intersected rows, it returns the following error: Unable to get the CountBlank property of the WorksheetFunction class (Run-time error '1004':).. .and it does work with "RANGE", "RANGE" kind of notation. It's too bad I was hoping it's all done :)))). Thanks anyway! :). all the best, Nile "Dave Peterson" wrote in message ... Be careful. It looks to me like you have a small bug in your code. This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete row if no data, non-contiguous range problem
I'm sure you could find lots of things about individual pieces (countblank and
areas, for example). But to find the combination of things sometimes becomes more difficult--there's probably lots, but to filter through all the hits could take awhile. Nile Gilmanov wrote: hah thank you Dave, now i am completely happy! :))) All the best, Nile PS I wonder why I could not find anything like this on the usenet archives, seems very practical to me :). "Dave Peterson" wrote in message ... You're correct about =countblank(). (I didn't try it.) But you could either just use worksheetfunction.countblank() twice -- once for each set of columns or you could loop through each area (nicer if you ever expand those columns to more non-contiguous areas): Option Explicit Sub testme() Dim myRng As Range Dim i As Long Dim myRng2 As Range Dim myBlankCtr As Long Dim myArea As Range With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) myBlankCtr = 0 For Each myArea In myRng2.Areas myBlankCtr = myBlankCtr + WorksheetFunction.CountBlank(myArea) Next myArea If myBlankCtr = myRng2.Cells.Count Then myRng(i).Delete End If Next i End With End Sub Nile Gilmanov wrote: Bah..... Dave I think that Worksheet function does not like intersected rows, it returns the following error: Unable to get the CountBlank property of the WorksheetFunction class (Run-time error '1004':).. .and it does work with "RANGE", "RANGE" kind of notation. It's too bad I was hoping it's all done :)))). Thanks anyway! :). all the best, Nile "Dave Peterson" wrote in message ... Be careful. It looks to me like you have a small bug in your code. This line: Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) isn't the same as: Set myRng2 = Intersect(myRng(i), .Range("A:B,D:K")) == (It's actually the same as: Set myRng2 = Intersect(myRng(i), .Range("A:K")) And I bet you wanted to avoid column C.) ======= And just a personal preference: Instead of: If WorksheetFunction.CountBlank(myRng2) = 10 Then I like: If WorksheetFunction.CountBlank(myRng2) = myrng2.cells.count Then if you're checking all the cells in that range. Then if you change the range, you don't have to remember to change that 10. Nile Gilmanov wrote: AWESOME this works very well... xcept i had to modify it just a smidge! I used COUNTBLANK function since it ignores any formulas that might be returning "" in the cells. :)))). With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("A:B", "D:K")) If WorksheetFunction.CountBlank(myRng2) = 10 Then myRng(i).Delete Else End If Next i End With "Dave Peterson" wrote in message ... I think I'd loop through the rows, but check each column that I wanted: Option Explicit Sub testme() Dim i As Long Dim myRng As Range Dim myRng2 As Range 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False End With With ActiveSheet Set myRng = .Rows("8:58") 'working backwords because deleting rows. For i = myRng.Rows.Count To 1 Step -1 Set myRng2 = Intersect(myRng(i), .Range("a:a,d:k")) If WorksheetFunction.CountA(myRng2) = 0 Then myRng(i).Delete End If Next i End With With Application .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With End Sub I also got rid of the selection stuff. And I could drop the .entirerow since I was dealing with the entire row to start. Nile Gilmanov wrote: Hi my name is Nile. I have the following code that checks every row from the bottom for any data and having found none deletes it, then goes on, it is limited to the certain range. It works fine with contiguous range such as ("A10:C20"), but does not work with non-contiguous ranges such as ("A10:C20, E10:G20")... can someone help me please? here is the code: -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- Dim i As Long 'turn off calculation and screenupdating. With Application .Calculation = xlCalculationManual .ScreenUpdating = False Range("A8:A58,D8:K58").Select 'working backwords because deleting rows. For i = Selection.Rows.Count To 1 Step -1 If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then Selection.Rows(i).EntireRow.Delete End If Next i .Calculation = xlCalculationAutomatic .ScreenUpdating = True End With -=-=-=-=--=-=-=-=-=-=-=-=-=-=-=-=-=- -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to automatically delete non-contiguous rows | Excel Discussion (Misc queries) | |||
Join non-contiguous ranges into one range via named range? | Excel Discussion (Misc queries) | |||
Adding Data to a Non-Contiguous Range in Excel 2007 | Charts and Charting in Excel | |||
Non-Contiguous Named Range? | Excel Discussion (Misc queries) | |||
Non Contiguous range and loops | Excel Programming |