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
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