-
Solved: loop not working
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.
[vba]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
[/vba]
-
You must delete from the bottom up and from right to left
Use
[VBA]
For i = iLastRow to 12 step -1
'and
For i = iLastcol to 3 Step -1
[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
hi mdmackillop
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.
-
Deleting from the top/left will not work properly if there are successive blank cells.
[vba]
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
[/vba]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
-
thank you very much for giving me solution & also improving my knowledge in working with deleteing rows & columns.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules