Quick Macro question - How to delete two rows then skip one - and repeat

D

David Smithz

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
 
D

David Smithz

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.
"May 7 2004 7:54AM"


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
 
D

Dave Peterson

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 = "mm/dd/yyyy hh:mm"
End With
Next myCell
End Sub

Select the range first and then run the macro.
 
D

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("sheet99").range("a:a").cells _
.specialcells(xlcelltypeblanks).entirerow.delete
on error goto 0

Change the sheet name (or use activesheet) and the column.
 
Top