I am an experienced MS Excel user and I am just now trying to learn
about using the functionality of macros. I am currently trying to
write a macro to do the following:

I have a spreadsheet that is set up in the following way:
The first row of data has an employee name in the futhest cell to the
left and then employee data, the second row has no employee name in the
first cell but there data related to that employee directly under the
data on the prior row. The third row is blank and the next row is
similar to row one and two except now employee name but it is still for
that employee, then another blank row. Once the next employees data
starts, the new employees name is printed in the cell once.

What I need is a macro to first delete all blank rows and , if
possible, a macro to copy the employee name down until the next
employee name appears. Once this is done I will then be able to sort
and run pivot table on the data.

Any help in writting the actual code would be GREATLY appreciated.

Thanks!
Gerald

If there are no blanks embedded in column B of the rows that contain data
then

dim rng as range, rng1 as range
columns(2).specialCells(xlblanks).EntireRow.Delete
set rng = columns(1).SpecialCells(xlBlanks)
rng.formula = "=" & rng(1).offset(-1,0).Address(0,0)
set rng1 = Range(cells(2,1),cells(2,1).End(xldown))
rng1.formula = rng1.Value

test on a copy of your data.
--
Regards,
Tom Ogilvy


"Gerald" wrote in message
oups.com...
I am an experienced MS Excel user and I am just now trying to learn
about using the functionality of macros. I am currently trying to
write a macro to do the following:

I have a spreadsheet that is set up in the following way:
The first row of data has an employee name in the futhest cell to the
left and then employee data, the second row has no employee name in the
first cell but there data related to that employee directly under the
data on the prior row. The third row is blank and the next row is
similar to row one and two except now employee name but it is still for
that employee, then another blank row. Once the next employees data
starts, the new employees name is printed in the cell once.

What I need is a macro to first delete all blank rows and , if
possible, a macro to copy the employee name down until the next
employee name appears. Once this is done I will then be able to sort
and run pivot table on the data.

Any help in writting the actual code would be GREATLY appreciated.

Thanks!
Gerald

I'm also quite ineperienced but to delete all blank rows I think thi
macro should work.

Sub Macro1(macro)
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) =
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Su

--
fugfu
-----------------------------------------------------------------------
fugfug's Profile: http://www.excelforum.com/member.php...fo&userid=2495
View this thread: http://www.excelforum.com/showthread.php?threadid=39458

try this. may not be elelgant not conventiona. see whether you succeed

suppose the blanks in a particular column(it does not matter if the other
coumns are blank)
highlight that range in that column
edit-goto-special-blanks
then all the blanks in that range will be slected
in the vbeditor you need not created as ub
but open intemetiate window and type
selection.entirerow.delete
and at the end of the above row click <enter

there is an addin called <error remover.which contains <delet blank rows
url is
www.jlxl.net

there are solutions to many other problems in this add in.


--
remove $$$ from email addresss to send email

=========
fugfug wrote in
message ...

I'm also quite ineperienced but to delete all blank rows I think this
macro should work.

Sub Macro1(macro)
Public Sub DeleteBlankRows()

Dim R As Long
Dim C As Range
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

If Selection.Rows.Count 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If
For R = Rng.Rows.Count To 1 Step -1
If Application.WorksheetFunction.CountA(Rng.Rows(R).E ntireRow) = 0
Then
Rng.Rows(R).EntireRow.Delete
End If
Next R

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub


--
fugfug
------------------------------------------------------------------------
fugfug's Profile:
http://www.excelforum.com/member.php...o&userid=24950
View this thread: http://www.excelforum.com/showthread...hreadid=394580