software

Hi there,

I have a spread sheet that has one row of information and then two blank
rows underneath. Previously I have manually gone through and changes all of
these, but as it keeps coming up I am fed up of doing this. It's time to
master macros. I did try and start a macro but basically I failed.

So how would I quickly achieve what I'm after.

Any input greatly appreciated.

Kind regards

Dave

quot;David Smithzquot; wrote in message

Also related to my previous posting is now that I want to change lots of
values in a cell from one format to another. In that currently they have
dates in them that Excel does not recognise as dates:
e.g.
quot;May 7 2004 7:54AMquot;Firstly I set all the cells types et to be a customer which has time and
date displayed (i.e. dd/mm/yyyy hh:mm).

Now if I rearrange the date to have the day first and put a space between
the time and the AM then Excel recognises the entry as a date and time.
Now I want to apply that to every cell in the spreadsheet without having to
do it manually each time. How would I write a macro for that because when I
tried I got strange results.

Thanks

Dave
VBA seems to be pretty forgiving with that format:

Option Explicit
Sub testme()
Dim myCell As Range
Dim myRng As Range

Set myRng = Selection
For Each myCell In myRng.Cells
With myCell
.Value = CDate(.Value)
.NumberFormat = quot;mm/dd/yyyy hh:mmquot;
End With
Next myCell
End Sub

Select the range first and then run the macro.

David Smithz wrote:
gt;
gt; quot;David Smithzquot; wrote in message
gt;
gt; Also related to my previous posting is now that I want to change lots of
gt; values in a cell from one format to another. In that currently they have
gt; dates in them that Excel does not recognise as dates:
gt; e.g.
gt; quot;May 7 2004 7:54AMquot;
gt;
gt; Firstly I set all the cells types et to be a customer which has time and
gt; date displayed (i.e. dd/mm/yyyy hh:mm).
gt;
gt; Now if I rearrange the date to have the day first and put a space between
gt; the time and the AM then Excel recognises the entry as a date and time.
gt; Now I want to apply that to every cell in the spreadsheet without having to
gt; do it manually each time. How would I write a macro for that because when I
gt; tried I got strange results.
gt;
gt; Thanks
gt;
gt; Dave

--

Dave Peterson

Can you just sort by a column that always has data. Those blanks will be
grouped at the bottom.

Or if you can pick out a column that always has info in it if the row is used:

on error resume next
worksheets(quot;sheet99quot;).range(quot;a:aquot;).cells _
.specialcells(xlcelltypeblanks).entirerow.delete
on error goto 0

Change the sheet name (or use activesheet) and the column.David Smithz wrote:
gt;
gt; Hi there,
gt;
gt; I have a spread sheet that has one row of information and then two blank
gt; rows underneath. Previously I have manually gone through and changes all of
gt; these, but as it keeps coming up I am fed up of doing this. It's time to
gt; master macros. I did try and start a macro but basically I failed.
gt;
gt; So how would I quickly achieve what I'm after.
gt;
gt; Any input greatly appreciated.
gt;
gt; Kind regards
gt;
gt; Dave

--

Dave Peterson