Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
StackemEvs;739887 Wrote: Hi, I want a very simple couple of lines which checks if the cells in the first column is blank (which means it has been added by mistake) and deletes it out. Just checking the first cell is enough. Examples I've tried a VBA Code: -------------------- 'Check if Column A blank If wksDataSheet.Cells(Cells(1, 1)) Like " " Then Selection.nrows(1).EntireRow.Delete Else End If If wksDataSheet.CountA(.Columns(nRow, 1)) = 0 Then .Columns(nRow, 1).EntireColumn.Delete End If If Application.CountA(.Columns(A)) = 0 Then .Columns(A).EntireColumn.Delete End If -------------------- Thanks VBA Code: -------------------- Sub DeleteBlankColA() Dim x As Integer 'Minimun row to check Dim x1 As Integer x1 = 1 For x = 1 To 2000 If Len(Trim(Cells(x1, 1))) < 1 Then Rows(x1).Delete x1 = x1 - 1 End If x1 = x1 + 1 Next x End Sub -------------------- Charles 'Opener Consulting Home' (http://www.openerconsulting.com) -- Charles C ------------------------------------------------------------------------ Charles C's Profile: http://www.thecodecage.com/forumz/member.php?u=89 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
Here's two ways:
Sub DeleteColumn() With Cells(1, 1) 'If Application.WorksheetFunction.CountBlank(.EntireCo lumn) = Rows.Count Then .EntireColumn.Delete 'If .Value = "" Then .EntireColumn.Delete End With End Sub Watch for wordwrap as both 'If' constructs are single lines. The 1st 'If' reflects your initial criteria of the column being blank. It counts the blank cells in the 1st column and compares that to the number of rows on the worksheet. If equal then the column is deleted. The 2nd 'If' checks if "A1" is empty and if so deletes the column. HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
Charles, why such a complex solution and with no explanation to the OP? they would have great trouble understanding the solution you provided. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
On 19 July, 22:24, Charles C wrote:
StackemEvs;739887 Wrote: Hi, *I want a very simple couple of lines which checks if the cells in the first column is blank (which means it has been added by mistake) and deletes it out. *Just checking the first cell is enough. *Examples I've tried a VBA Code: -------------------- * * 'Check if Column A blank * If wksDataSheet.Cells(Cells(1, 1)) Like " " Then * Selection.nrows(1).EntireRow.Delete * Else * End If * * If wksDataSheet.CountA(.Columns(nRow, 1)) = 0 Then * .Columns(nRow, 1).EntireColumn.Delete * End If * * If Application.CountA(.Columns(A)) = 0 Then * .Columns(A).EntireColumn.Delete * End If -------------------- Thanks VBA Code: -------------------- * Sub DeleteBlankColA() * Dim x As Integer 'Minimun row to check * Dim x1 As Integer * x1 = 1 * For x = 1 To 2000 * If Len(Trim(Cells(x1, 1))) < 1 Then * Rows(x1).Delete * x1 = x1 - 1 * End If * x1 = x1 + 1 * Next x * End Sub -------------------- Charles 'Opener Consulting Home' (http://www.openerconsulting.com) -- Charles C ------------------------------------------------------------------------ Charles C's Profile:http://www.thecodecage.com/forumz/member.php?u=89 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- another way. If [Sheet1].[A1] = "" Then [A:A].EntireColumn.Delete regards FSt1 End If |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
Or simply this one-liner:
Sub DeleteRows() Columns("A").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub Regards, Per On 20 Jul., 06:10, FSt1 wrote: On 19 July, 22:24, Charles C wrote: StackemEvs;739887 Wrote: Hi, *I want a very simple couple of lines which checks if the cells in the first column is blank (which means it has been added by mistake) and deletes it out. *Just checking the first cell is enough. *Examples I've tried a VBA Code: -------------------- * * 'Check if Column A blank * If wksDataSheet.Cells(Cells(1, 1)) Like " " Then * Selection.nrows(1).EntireRow.Delete * Else * End If * * If wksDataSheet.CountA(.Columns(nRow, 1)) = 0 Then * .Columns(nRow, 1).EntireColumn.Delete * End If * * If Application.CountA(.Columns(A)) = 0 Then * .Columns(A).EntireColumn.Delete * End If -------------------- Thanks VBA Code: -------------------- * Sub DeleteBlankColA() * Dim x As Integer 'Minimun row to check * Dim x1 As Integer * x1 = 1 * For x = 1 To 2000 * If Len(Trim(Cells(x1, 1))) < 1 Then * Rows(x1).Delete * x1 = x1 - 1 * End If * x1 = x1 + 1 * Next x * End Sub -------------------- Charles 'Opener Consulting Home' (http://www.openerconsulting.com) -- Charles C ------------------------------------------------------------------------ Charles C's Profile:http://www.thecodecage.com/forumz/member.php?u=89 View this thread:http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- another way. If [Sheet1].[A1] = "" Then * * [A:A].EntireColumn.Delete regards FSt1 End If- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
I agree with Simon. 1)Code to delete Rows should start from the end of the data and work backwards 2)Looping to remove rows is the most inefficient way to do this action. 2)Your code will Loop 2000 times, and it uses two variables where one is all that is needed. You could use AutoFilter to filter for blanks then delete the resulting filtered range -no VBA necessary. I would slightly amend Simon's code to just check only the UsedRange VBA Code: -------------------- Sub remove_rows_if_a_is_blank() With Sheet1.UsedRange '<-change the sheet as required .Columns(1).SpecialCells(xlCellTypeBlanks).EntireR ow.Delete End With End Sub -------------------- -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?u=15 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
royUK;739993 Wrote: I agree with Simon. 1)Code to delete Rows should start from the end of the data and work backwards 2)Looping to remove rows is the most inefficient way to do this action. 2)Your code will Loop 2000 times, and it uses two variables where one is all that is needed. You could use AutoFilter to filter for blanks then delete the resulting filtered range -no VBA necessary. I would slightly amend Simon's code to just check only the UsedRange VBA Code: -------------------- Sub remove_rows_if_a_is_blank() With Sheet1.UsedRange '<-change the sheet as required Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete End With End Sub -------------------- Good catch Roy, i've never checked but wouldn't the use of specialcells only cater for the used range? -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
Simon Lloyd;739994 Wrote: Good catch Roy, i've never checked but wouldn't the use of specialcells only cater for the used range? Not sure about that Simon,I always reduce the range by using UsedRange or similar.I can't say that I have seen any mention that Specialcells limits itself to the UsedRange. It might be that CurrentRegion would be better in case there are problems with UsedRange being bigger than the data,e.g. if the Last Cell is not actually within the data -- royUK Hope that helps, RoyUK For tips & examples visit my 'web site' (http://www.excel-it.com/) ------------------------------------------------------------------------ royUK's Profile: http://www.thecodecage.com/forumz/member.php?u=15 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Deleting blank columns
royUK;739995 Wrote: Not sure about that Simon,I always reduce the range by using UsedRange or similar.I can't say that I have seen any mention that Specialcells limits itself to the UsedRange. It might be that CurrentRegion would be better in case there are problems with UsedRange being bigger than the data,e.g. if the Last Cell is not actually within the data Yes CurrentRegion would be much better...........even better catch! :) -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=207689 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reduce excel file size by deleting blank rows and columns?? | Excel Discussion (Misc queries) | |||
easy script for deleting blank columns | Excel Programming | |||
Deleting blank rows which contain blank drop-down list boxes | Excel Programming | |||
Deleting blank cells w/o impacting other rows/columns | Excel Programming | |||
Newbee - deleting blank columns | Excel Programming |