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

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

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

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

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 -

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

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

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

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