Thanks a lot don
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1
Sent: Wednesday, March 28, 2012 6:18 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN
Should do it.
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguille...@gmail.com
From: Siraj Momin (BTG) mailto:smo...@ccc.ae
Sent: Wednesday, March 28, 2012 2:36 AM
To: excel-macros@googlegroups.com
Subject: RE: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN
Dear Don,
Your macro did the job, need a further help if you can spare time for
this, I need to make borders as shown in the sample output sheet as the
data is big I want to automate the process of border..
Also in the file raw data sheet to be converted as show in the sample
output sheet.
It will be a big help for me...
Siraj
From: excel-macros@googlegroups.com
[mailto:excel-macros@googlegroups.com] On Behalf Of dguillett1
Sent: Tuesday, March 27, 2012 4:52 PM
To: excel-macros@googlegroups.com
Subject: Re: $$Excel-Macros$$ Re: DELETE BLANK ROW AND COLUMN
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 mailto:yiqiang@gmail.com
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