Although usually not maybe in this case deleting one row/column at a time from
the bottom up or last col to left ????? Worth a try.
Sub DeleteBlankRowsAndColumnsOneAtATime()
Dim i As Long
Application.Calculation = xlManual
For i = Cells.SpecialCells(xlLastCell).Row To 1 Step -1
If Application.CountA(Rows(i)) < 1 Then Rows(i).Delete
Next i
For i = Cells.SpecialCells(xlLastCell).Column To 1 Step -1
If Application.CountA(Columns(i)) < 1 Then Columns(i).Delete
Next i
Application.Calculation = xlAutomatic
End Sub
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Garcia
Sent: Monday, March 26, 2012 9:25 PM
To: excel-macros@googlegroups.com
Subject: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN
Sub delrowcolumn()
Dim Area_ As Range
Dim Rng1 As Range
Dim Rng2 As Range
Dim Ro As Long
Dim Col As Integer
Dim lstRo As Long
Dim lstCol As Integer
If [a1] = "" Then [a1] = 1
Set Area_ = ActiveSheet.UsedRange
[a1].ClearContents
lstRo = Area_.rows.Count
lstCol = Area_.Columns.Count
Application.ScreenUpdating = False
For Ro = 1 To lstRo
If Application.CountA(Area_.rows(Ro)) = 0 Then
If Rng1 Is Nothing Then
Set Rng1 = rows(Ro)
Else
Set Rng1 = Union(Rng1, rows(Ro))
End If
End If
Next
For Col = 1 To lstCol
If Application.CountA(Area_.Columns(Col)) = 0 Then
If Rng2 Is Nothing Then
Set Rng2 = Columns(Col)
Else
Set Rng2 = Union(Rng2, Columns(Col))
End If
End If
Next
On Error Resume Next
Rng1.EntireRow.Delete
Rng2.EntireColumn.Delete
Set Area_ = Nothing
Set Rng1 = Nothing
Set Rng2 = Nothing
Application.ScreenUpdating = True
End Sub
On Monday, March 26, 2012 8:16:10 PM UTC+8, Siraj Momin (BTG) wrote:
Dear Friends
I need to delete blank row and blank column from the attached file with the
help of macro because data is large.
Thanks
Siraj
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
--
FORUM RULES (986+ members already BANNED for violation)
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Cross-promotion of, or links to, forums competitive to this forum in
signatures are prohibited.
NOTE : Don't ever post personal or confidential data in a workbook. Forum
owners and members are not responsible for any loss.
------------------------------------------------------------------------------------------------------
To post to this group, send email to excel-macros@googlegroups.com
