PDA

View Full Version : Solved: loop not working



anandbohra
08-13-2007, 04:58 AM
hi all
i made 2 loop first one delete all rows which are empty & second delete all columns which are empty
now i want to run both the loop one by one in all sheets how to do it

pl suggest amendments in my code.

Option Explicit
Sub delete_row()
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String
With ActiveSheet
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 12 To iLastRow
If .Cells(i, "A").Value = "" Then
.Cells(i, "A").EntireRow.Delete
End If
Next i

End With
Range("a1").Select
End Sub
Sub delete_column()
Dim iLastcol
Dim i As Long
Dim sMsg As String
With ActiveSheet
iLastcol = Cells(12, 256).End(xlToLeft).Column
For i = 3 To iLastcol
If .Cells(12, i).Value = "" Then
.Cells(12, i).EntireColumn.Delete
End If
Next i
End With
Range("a1").Select
End Sub
Sub total_loop()
Dim wks
For Each wks In ActiveWorkbook.Sheets
Call delete_row
Call delete_column
Next wks
End Sub

mdmackillop
08-13-2007, 05:05 AM
You must delete from the bottom up and from right to left
Use

For i = iLastRow to 12 step -1
'and

For i = iLastcol to 3 Step -1

anandbohra
08-13-2007, 05:20 AM
hi mdmackillop (http://www.vbaexpress.com/forum/member.php?u=87)

my delete row & delete column loop is working but when i run both proceedure in total loop it is not working

say if i do individually in one sheet
i run delete_row it runs perfectly
then i run delete_column it runs perfect

but when i run total_loop it runs only in activesheet.
so my query is how to run both the proceedure in all sheets.

mdmackillop
08-13-2007, 05:46 AM
Deleting from the top/left will not work properly if there are successive blank cells.

Option Explicit
Sub total_loop()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Sheets
Call delete_row(wks)
Call delete_column(wks)
Next wks
End Sub

Sub delete_row(wks As Worksheet)
Dim iLastRow As Long
Dim i As Long
Dim sMsg As String
wks.Activate
With wks
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 12 Step -1
If .Cells(i, "A").Value = "" Then
.Cells(i, "A").EntireRow.Delete
End If
Next i

End With
Range("a1").Select
End Sub

Sub delete_column(wks As Worksheet)
Dim iLastcol
Dim i As Long
Dim sMsg As String
wks.Activate
With wks
iLastcol = .Cells(12, 256).End(xlToLeft).Column
For i = iLastcol To 3 Step -1
If .Cells(12, i).Value = "" Then
.Cells(12, i).EntireColumn.Delete
End If
Next i
End With
Range("a1").Select
End Sub

anandbohra
08-13-2007, 05:56 AM
thank you very much for giving me solution & also improving my knowledge in working with deleteing rows & columns.


:friends::bow: