HowTo remove unused rows?

  • Thread starter Thread starter soundsalarming
  • Start date Start date
S

soundsalarming

I am paseting information into excel from an HTML table, this gives me
large amount of unused rows. Is there a way to remove all unused row
without having to highlight each one
 
What do you mean unused? - Do you mean blank rows between your data, or do you
mean the other 60,000+ blank rows at the bottom of the data. If the fomer then
there are various way to delete those blank rows, but if you mean the latter,
then no, you have 65,536 rows regardless, and whilst they could be hidden, they
cannot be deleted so as to get rid of them permanently. If you do delete them,
they will simply be replaced by new ones.
 
Depending on your version of Excel, you will get different things
happening. But you would need to use macros for most such
things.

Here are some links that might help.
help with deleting empty rows
http://www.mvps.org/dmcritchie/excel/delempty.htm

there are several macros: DEL95HTMLemptyCells is
particularly good for Excel 95 which wasn't as good at this.

Some other problems with data pasted from HTML may be
the nonbreaking space characer ( ) and some graphics.
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
delShapesOnSht within
http://www.mvps.org/dmcritchie/excel/shapes.htm
 
OK, now lets clarify what is meant by blank rows between data :-)

Is the entire row blank for each of these rows? Is theer a column anywhere on
the sheet that you can guarantee will have data if it is NOT to be deleted. If
so then simply select that column down to the end of your data and do Edit / Go
To / Special / Blanks - Then do Edit / Delete / Entire row.

If not, then there are still VBA routines that will accomplish this, but the
trick is to decide on a consistent logic of determining which rows can be
deleted.
 
OK, let me clarify.

I import data from an html table using cut'n'paste, when the data i
inserted it leaves blank rows in between data rows, e.g. row 1 ha
data, row 2 is empty, row 3 has data etc etc.

Currently, to remove the blank rows I hold down the ctrl key, highligh
each blank row, right click and click delete. This works fine, but eac
table could have 200 rows of data, and I have at least 300 tables t
import.

The question I was asking was is there a way to aytomate this process
either with a macro or some other method
 
The method I gave you in my last note should do that in one hit, regardless of
how many rows.

Pick any column where you can say if the cell in that column is blank, then the
row can be deleted (Lets assume it's Col B. Now if the range of your data is
say 1000 rows, then simply select B1:B1000, do Edit / Go To / Special / Blanks,
then do Edit / Delete / Entire Row. Job Done.

If you prefer a macro then here are a couple of macros:-

Public Sub DeleteBlankRows1()
On Error Resume Next
Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

or

Public Sub DeleteBlankRows2()
Dim rRow As Range
Dim rDelete As Range
For Each rRow In ActiveSheet.UsedRange.Rows
If Application.CountA(rRow) = 0 Then
If rDelete Is Nothing Then
Set rDelete = rRow
Else
Set rDelete = Union(rDelete, rRow)
End If
End If
Next rRow
If Not rDelete Is Nothing Then _
rDelete.EntireRow.Delete
End Sub
 
Thanks Ken that did the trick (go to > blank).

Whilst were on the subject of importing from html I'm having problem
with date formats.

The data that i am importing has a from and to date, in the forma
98-99 or 01-03 etc.

The 98-03 imports fine but 01-03 gets changed to 01-Mar and when i sav
it to csv it changes again to 38048.

I'm sure this is something simple but I cant seem to resolve it.

Thanks
Craig
 
Back
Top