I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

Joel,

I tried it and it worked on that worksheet... one more question, what do I
need to do to delete the info from column F after it is moved to column C?

Thanks,
--
Malissa


"Joel" wrote:

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

Here is a simply methods which I added the delete for column f

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow_F).Copy
Range("C" & Row_C_Count).PasteSpecial _
SkipBlanks:=True
columns("F").delete

"Malissa" wrote:

Joel,

I tried it and it worked on that worksheet... one more question, what do I
need to do to delete the info from column F after it is moved to column C?

Thanks,
--
Malissa


"Joel" wrote:

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

Joel,

I recorded my keystrokes to create the first part and this is what it looks
like:

Selection.EntireColumn.Insert
Range("D1").Select
Selection.Copy
ActiveCell.SpecialCells(xlLastCell).Select
Range("A14").Select
Range(Selection, Cells(1)).Select
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
Selection.EntireRow.Delete

Is there a better way? Thanks so much for your help!

--
Malissa


"Joel" wrote:

Here is a simply methods which I added the delete for column f

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow_F).Copy
Range("C" & Row_C_Count).PasteSpecial _
SkipBlanks:=True
columns("F").delete

"Malissa" wrote:

Joel,

I tried it and it worked on that worksheet... one more question, what do I
need to do to delete the info from column F after it is moved to column C?

Thanks,
--
Malissa


"Joel" wrote:

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

I just discovered that the first part doesnt always work.. depending on the
size of the worksheet. I am seeing that my keystrokes did reference specific
cells. The part you did adding the delete F is now allowing a blank field
when pasting into column C.
--
Malissa


"Joel" wrote:

Here is a simply methods which I added the delete for column f

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow_F).Copy
Range("C" & Row_C_Count).PasteSpecial _
SkipBlanks:=True
columns("F").delete

"Malissa" wrote:

Joel,

I tried it and it worked on that worksheet... one more question, what do I
need to do to delete the info from column F after it is moved to column C?

Thanks,
--
Malissa


"Joel" wrote:

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa

3 things

1) Once column F is deleted you can't rerun the code a 2nd time. When using
macros, copy the original sheet to a temporary sheet to save your original
data. Then copy back to temporary sheet to the original shee as you develope
your macro. I usually just hightlight the gray cell between Row 1 and Column
A to hight the entrie worksheet. Then go to a tempory worksheet and press
the same gray key and paste. then copy the temp sheet back to the original
as I'm developiong the macro

2) Not sure why column F is blank. Is it because it was deleted did the
column change when you inserted a new column.

3) I modified you recorded code. I don't think it is exactly what you want.
I didn't know which column to Insert so there is a question mark below.
Change to the correct column. I also think you wanted the Last Row in Column
A, not A14 so I made a simple change. The code was deleteing column A after
you put data into column A. Doesn't make sense.

'insert column Not sure which column Because
'you code started with a column already selected
Columns("?").Insert
'get LastRow in Column A
LastRow_A = Range("A" & Rows.Count).End(xlUp).Row
'Copy D1 to column A, rows 1 to Last Row
Range("D1").Copy _
Destination:=Range("A1:A" & LastRow)
'Do you really want to delete column A
Columns("A").Delete

If you have more problems post all the code so I can see how you are
progressing.
"Malissa" wrote:

I just discovered that the first part doesnt always work.. depending on the
size of the worksheet. I am seeing that my keystrokes did reference specific
cells. The part you did adding the delete F is now allowing a blank field
when pasting into column C.
--
Malissa


"Joel" wrote:

Here is a simply methods which I added the delete for column f

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
Range("F1:F" & LastRow_F).Copy
Range("C" & Row_C_Count).PasteSpecial _
SkipBlanks:=True
columns("F").delete

"Malissa" wrote:

Joel,

I tried it and it worked on that worksheet... one more question, what do I
need to do to delete the info from column F after it is moved to column C?

Thanks,
--
Malissa


"Joel" wrote:

I start check column F at Row 1. this may need to be changed.

LastRow_C = Range("C" & Rows.Count).End(xlUp).Row
Row_C_Count = LastRow_C + 1
LastRow_F = Range("F" & Rows.Count).End(xlUp).Row
For F_RowCount = 1 To LastRow_F
If Range("F" & F_RowCount) < "" Then
Range("C" & Row_C_Count) = Range("F" & F_RowCount)
Row_C_Count = Row_C_Count + 1
End If
Next F_RowCount

"Malissa" wrote:

I have a worksheet that starts out like this with 5 columns:

A B C D
E
1000 0 14630.17 Bank of Amer Disbursement Account
2200 0 250.64 401K Pension Due
6220 5192.31 0 Staff Veterinary Salaries 0
6223 1179.74 0 Veterinary Assistant Wages 157.81
6224 908.5 0 Receptionist Wages 79
6225 4843.19 0 Veterinary Technician Wages 410.98
6227 1346.15 0 Practice Manager Wages 0
6245 262 0 Grooming Payroll Expenses 0
6290 1380 0 Payroll Taxes
6290 0 3525.19 Payroll Taxes-ee
6290 3525.19 0 Payroll Taxes-ee
6315 0 14.4 Disability Insurance
6330 0 342 Medical/Dental Insurance
6355 125.32 0 Retirement Plan Contributions

The first part of the marco I have created creates a new column before A and
takes the amount in row 1column C and pastes it to all rows that hold
information then deletes first row.
Looks like this:

A B C D E
F
14630.17 2200 0 250.64 401K Pension Due
14630.17 6220 5192.31 0 Staff Veterinary Salaries 0
14630.17 6223 1179.74 0 Veterinary Asst Wage 157.81
14630.17 6224 908.5 0 Receptionist Wages 79
14630.17 6225 4843.19 0 Veterinary TechWages 410.98
14630.17 6227 1346.15 0 Practice Manager Wages 0
14630.17 6245 262 0 Grooming Payroll Expenses 0
14630.17 6290 1380 0 Payroll Taxes
14630.17 6290 0 3525.19 Payroll Taxes-ee
14630.17 6290 3525.19 0 Payroll Taxes-ee
14630.17 6315 0 14.4 Disability Insurance
14630.17 6330 0 342 Medical/Dental Insurance
14630.17 6355 125.32 0 Retirement Plan Contributions

The next part I am unsure of. What I need to do is take the amounts in
column F and move them to the bottom of column C. I also need to eliminate
spaces. The catch is that this document may never be exactly the same so it
cannot be cell specific.

Any suggestions are greatly appreciated. This is my first time working with
macros. If you need more info please let me know.



Thanks,
--
Malissa