PDA

View Full Version : Solved: Using Statemant CASE to delete rows in multiple sheets



elsg
07-15-2013, 11:40 AM
Hellow

I need delete row multiple sheet, case in column A find empty cells
Sub DeleteRow()
Dim Sh As Worksheet

For Each Sh In ActiveWorkbook.Worksheets
Select Case Sh.Name
Case "Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro"
Range("A4:A12").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Select

Next Sh
End Sub

rollis13
07-15-2013, 02:12 PM
Could something like this be usefull:
Option Explicit
Sub DeleteRow()
Dim Sh, vSheets

vSheets = Array("Janeiro", "Fevereiro", "Março", "Abril", "Maio", "Junho", "Julho", "Agosto", "Setembro", "Outubro", "Novembro", "Dezembro")
For Each Sh In vSheets
With Sheets(Sh)
On Error Resume Next 'keep going if no blank rows found in range
.Range("A4:A12").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
Next Sh

End Sub

elsg
07-15-2013, 02:36 PM
i like it

Verey very good!!

Thank you!!!

snb
07-16-2013, 03:51 AM
or

Sub M_snb()
On Error Resume Next


For Each sh In Sheets(Application.GetCustomListContents(4))
sh.Range("A4:A12").SpecialCells(4).EntireRow.Delete
Next
End Sub

Aussiebear
07-16-2013, 04:11 AM
Tight code yet again.... BUT where does the Custom lists get formed from your code? People have trouble learning from this example if it doesn't carry any explanation with the suggested content.

snb
07-16-2013, 04:32 AM
@Aussiebear

The nice thing of this 'custom'list is that it is not 'custom' made but builtin in Excel. Therefore it's for everyone the same (depending of the international settings). That makes it very robust.

You want to know more about customlists ?
see http://www.snb-vba.eu/VBA_Excel_customlist_en.html

rollis13
07-16-2013, 10:50 AM
@elsg, glad being of some help :biggrin:.

@snb, how can I use my italian Excel if I need to name the tabs using english ? Maybe, I could create a custom list but how do I know its' number to be used in your script ?

snb
07-16-2013, 12:09 PM
@rollis
In that case you need to concoct something


Sub M_snb()
On Error Resume Next

For Each sh In Sheets([transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))])
sh.Range("A4:A12").SpecialCells(4).EntireRow.Delete
Next
End Sub

or if you prefer customlists:


Sub M_snb()
With Application
.AddCustomList [transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))]
For Each sh In Sheets(.GetCustomListContents(.CustomListCount))
sh.range("A4:A12").specialcells(4).entirerow.delete
Next
.DeleteCustomList .CustomListCount
end with
End Sub

rollis13
07-16-2013, 01:01 PM
I understand that I shouldn't be hijacking elsg's thread ...
but can't get:For Each sh In Sheets([transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))]) to work, "sh" is always empty. If I use the second version the CustomList #5 is created so .AddCustomList [transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))] is working but the "sh" is still empty.

mancubus
07-16-2013, 01:05 PM
just did a google search to learn the number:


Sub Custom_list()
Application.AddCustomList Array("aaa", "bbb", "ccc")
MsgBox Application.GetCustomListNum(Array("aaa", "bbb", "ccc"))
End Sub


http://msdn.microsoft.com/en-us/library/office/ff838809.aspx

rollis13
07-16-2013, 01:12 PM
Thanks mancubus, should have done a search and probably would have come up with a .GetCustomListNum :blush .

mancubus
07-16-2013, 01:29 PM
Thanks mancubus, should have done a search and probably would have come up with a .GetCustomListNum :blush .

you are welcome.

but if you follow snb's procedure (add, use, then delete the custom list) you dont need to get that number. because CustomListCount retuns the total number of the custom lists and the last number represents the last list.

snb
07-17-2013, 01:42 AM
I understand that I shouldn't be hijacking elsg's thread ...
but can't get:For Each sh In Sheets([transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))]) to work, "sh" is always empty. If I use the second version the CustomList #5 is created so .AddCustomList [transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))] is working but the "sh" is still empty.

sh is an item in the collection 'sheets' so it's an object.
sh.name would give you "January", "February", etc.
You can test using:


Sub M_snb()
On Error Resume Next

For Each sh In Sheets([transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))])
msgbox sh.name
sh.Range("A4:A12").SpecialCells(4).EntireRow.Delete
Next
End Sub

rollis13
07-17-2013, 09:53 AM
Sorry snb, but can't get it to work. I already had tried with "MsgBox sh" which, as said, came up with an empty box; if I use "MsgBox sh.Name" the box doesn't even show up. Without "On Error Resume Next" I get a "Runtime error: 9".

snb
07-17-2013, 10:06 AM
I doubt whether you have any sheets that contain the correct names:


Sub M_snb()
On Error Resume Next
For Each it In [transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))]
sheets.add.name=it
Next

For Each sh In Sheets([transpose(text(date(2013,row(1:12),1),"[$-409]mmmm"))])
msgbox sh.name
sh.Range("A4:A12").SpecialCells(4).EntireRow.Delete
Next
End Sub

rollis13
07-17-2013, 10:40 AM
To be true, for testing, I had created only 3 new sheets (correct english names). Now that your script creates all the 12 sheets (with names) the macro works.
If I delete 2 sheets (ex. March, June) and don't use your loop with "Sheets.Add.Name = it" again the macro doesn't work.
This means that there must be at least 12 sheets. So, if I don't need August because it's a full holiday month I'll have to create it as a dummy hidden sheet.
Now it makes sense :content: . Thanks for helping on use of CustomList in VBA.