Hello-

In a nutshell I am trying to figure out how to automatically sort name
and delete blank cells. Here is what I am doing...

I made a simple mock-up of the type of Excel data I am working wit
(below). It contains employee ID number, names, gross pay and wher
each employee did work for the month. The employee information i
entered in the excel sheet, along with each location they did work fo
during the month.

[image: http://www.vidbusters.com/media/excel_sample1.jpg]

This data is then brought to the following sheet through some simple I
statements (basically, if they did work for Location 1, then put th
amount there...).

[image: http://www.vidbusters.com/media/excel_sample2.jpg]

The problem is that when an employee does not do work for a certai
location, it just leaves a blank space. And in the real sheet, ther
are hundreds of employees so the page will have a few employees,
bunch of blank rows, then a few more employees.

I only know enough Excel to get by, my knowledge basically caps of
with VLOOKUPS :) Is there code or some sort of a macro that ca
delete those unused cells and sort the names so they are right unde
each other? If anyone could help, that would be awesome! Thanks i
advance

--
Message posted from http://www.ExcelForum.com

I created a sample worksheet with blank rows interspersed among the data
rows. Simply using DataSort put all my data in ascending order with all
the blank spaces at the bottom. Is this useful to you?

Ed

"doublef " wrote in message
...
Hello-

In a nutshell I am trying to figure out how to automatically sort names
and delete blank cells. Here is what I am doing...

I made a simple mock-up of the type of Excel data I am working with
(below). It contains employee ID number, names, gross pay and where
each employee did work for the month. The employee information is
entered in the excel sheet, along with each location they did work for
during the month.

[image: http://www.vidbusters.com/media/excel_sample1.jpg]

This data is then brought to the following sheet through some simple IF
statements (basically, if they did work for Location 1, then put the
amount there...).

[image: http://www.vidbusters.com/media/excel_sample2.jpg]

The problem is that when an employee does not do work for a certain
location, it just leaves a blank space. And in the real sheet, there
are hundreds of employees so the page will have a few employees, a
bunch of blank rows, then a few more employees.

I only know enough Excel to get by, my knowledge basically caps off
with VLOOKUPS :) Is there code or some sort of a macro that can
delete those unused cells and sort the names so they are right under
each other? If anyone could help, that would be awesome! Thanks in
advance.


---
Message posted from http://www.ExcelForum.com/

This rudimentary code will loop through your worksheet and
delete blank rows. It will leave the blank rows after
the "Total" lines.

Sub DelBlankRows()
Application.ScreenUpdating = False
Dim NumRows As Integer
Dim Iloop As Integer
NumRows = Range("A65536").End(xlUp).Row
For Iloop = 1 To NumRows
If Cells(Iloop, 1) = "" Then
If UCase(Cells(Iloop - 1, 1)) < "TOTAL" Then
Rows(Iloop).Delete
End If
End If
Next Iloop
End Sub

-----Original Message-----
Hello-

In a nutshell I am trying to figure out how to
automatically sort names
and delete blank cells. Here is what I am doing...

I made a simple mock-up of the type of Excel data I am
working with
(below). It contains employee ID number, names, gross
pay and where
each employee did work for the month. The employee
information is
entered in the excel sheet, along with each location they
did work for
during the month.

[image:
http://www.vidbusters.com/media/excel_sample1.jpg]

This data is then brought to the following sheet through
some simple IF
statements (basically, if they did work for Location 1,
then put the
amount there...).

[image:
http://www.vidbusters.com/media/excel_sample2.jpg]

The problem is that when an employee does not do work for
a certain
location, it just leaves a blank space. And in the real
sheet, there
are hundreds of employees so the page will have a few
employees, a
bunch of blank rows, then a few more employees.

I only know enough Excel to get by, my knowledge
basically caps off
with VLOOKUPS :) Is there code or some sort of a macro
that can
delete those unused cells and sort the names so they are
right under
each other? If anyone could help, that would be awesome!
Thanks in
advance.


---
Message posted from http://www.ExcelForum.com/

.