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