set number of rows equal in mutiple columns

I am having trouble setting the number of rows equal across my spreadsheet. For example i have about 2000 records in column b and there should be that many rows in column d...however there are zero's goin all the way down my sheet (to file 65536). What i want to do is write something in vb so set number of rows in column b and make that many rows in all the other columns. It has to be variable because across my 3 spreadsheet pages the number of files varies depeding on the amount of information that i import. I have been working on this for a few days and it seems to me it should be a simple solution but I can't find it...thanks for any help you could give

Do I understand you correctly that you have (approximately) 2000 cells in column B that contain data, and that you expected a similar number of cells in column D to contain data?

The appearance of zeroes down to the last possible row (65535) indicates that you have entered something with column D selected, or that you have written something in VB that has corrupted the column.

What data did you expect to be in column D?

Was the data in column B imported or manually entered, and were you expecting more columns to be occupied?


To remove the zeroes that are currently in column D - if the whole column is zeroes, you can select the column (click on the 'D' column header to highlight the whole column) then, with your pointer in the shaded area, click the Right-mouse button and select Delete.

If column D contains some good data and you just want to remove rows (say) 2001 onwards, then enter 2001:65535 in the Name box (the small box that shows which cell you are currently in) and rows 2001 onwards should highlight. With your pointer in the highlighted area click the RightMouse button and select Delete.

Does this answer your question?

I can't just delete the rows b/c there is going to be a variable amount of rows in worksheets 1, 2,3. What my goal is is to make however many rows are in column b, that many rows are across the whole spreadsheet. i.e. if column b has 10 rows so does columns a,c,d. By the way i have excel 2003 i think that may be part of the problem I am not sure if some of the codes i have been given work in other versions or what.

the zero's dont take up the entire column but after info stops in column b, instead of nothing showing in column d zero's just appear.

i am not sure of this makes it any more clear or not...i hope so but thanks for the response.

It doesn't make it clear to me, no.

A Row is a collection of cells in a horizointal line across the page.

A Column is a collection of cells in a vertical line down the page.

This is true whether the cells contain data or not.

Are you trying to say that the number of data cells containing data that are in column B should equal the number of cells containing data that are in column D ?

If your data is not sensitive perhaps you could attach the workbook, or a sheet from it.

"Are you trying to say that the number of data cells containing data that are in column B should equal the number of cells containing data that are in column D"

yes..that is exactly what i was trying to say, sorry for the confusion. like i said i am not the most computer savy person in the world...sorry for the confusion

OK - data is usually :-

1. manually entered, a person types a row of new data or

2. formula extracted, a person types in column B and a formula extracts a portion of that data to column D (ie, =Left(b1,6)) or (=b1*C1) where B1 contains a quantity and C1 contains a rate etc or

3. data is imported, usually in the form of a .csv (comma delimited) file, or a text (tab or space delimited) file, and the import function (or Text-To-Columns function) separates the data across columns.


How was your data in column B derived, and why do you think there should be data in column D ?