set a column length
 

I need to make column D in my database equal to the number of records i
column B, which will change each time I update the data or change th
search criterea. How exactly do I go about this? This is the what
have now but it brings me the files themselve from B and not the lengt
which is what I want.

Stephe
Pear

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

set a column length
 

maybe this is what you are looking for
Sub setcollen()
Set myrange = Range("d2:d" & Cells(Rows.Count, "b").End(xlUp).Row)
End Sub

--
Don Guillett
SalesAid Software

"speary" wrote in
message ...

I need to make column D in my database equal to the number of records in
column B, which will change each time I update the data or change the
search criterea. How exactly do I go about this? This is the what i
have now but it brings me the files themselve from B and not the length
which is what I want.

Stephen
Peary


--
speary
------------------------------------------------------------------------
speary's Profile:
http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

This bit of code is fairly straight forward, basically it counts the records
in column B down to the last record and then widens the D column to that
width. So if there were 10 records in column B, column D becomes 10 in width

Dim rcount As Integer

Range("B:B").Select
Range(Selection, Selection.End(xlDown)).Select

For Each cell In Selection
rcount = rcount + 1
Next cell

Columns("D:D").Select
Selection.ColumnWidth = rcount



"speary" wrote:


I need to make column D in my database equal to the number of records in
column B, which will change each time I update the data or change the
search criterea. How exactly do I go about this? This is the what i
have now but it brings me the files themselve from B and not the length
which is what I want.

Stephen
Peary


--
speary
------------------------------------------------------------------------
speary's Profile: http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

It still seems to set a column length un equal to column 'b'. Is there a
place that I should add the formula to so that it sets the column length
when i first enter in my infor to VB..i tired running it after i had
completed my previous macro but it still had "0"'s going all the way to
the bottom of the spreadsheet b/c i must have copied the formula down
the entire column. How would I copy it just to the end of the number of
rows that contain information. for example:

If column b has 20 rows....so would column d


but in sheet 2

column b has 30 rows....so therefor column d has the same number


--
speary
------------------------------------------------------------------------
speary's Profile: http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

thanks to everyone for your responses...but all the options seem not t
work. Most return error messages. Maybe I am not explaining it right
am not sure, i am new to this visual basic stuff in excel. I guess wha
i wanted to do was make the number of rows in colum b equal the numbe
of rows in column d in 3 different sheets. Number of rows in column
has to equal b as well. I'm not sure if this helps or not but thank
again for responding

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

set a column length
 

does this idea help?
sub setlength
x=cells(rows.count,"d").end(xlup).row
set sh1=sheets("sheet1").range("b2:b"&x)
set sh2=sheets("sheet2").range("b2:b"&x)
etc

--
Don Guillett
SalesAid Software

"speary" wrote in
message ...

thanks to everyone for your responses...but all the options seem not to
work. Most return error messages. Maybe I am not explaining it right I
am not sure, i am new to this visual basic stuff in excel. I guess what
i wanted to do was make the number of rows in colum b equal the number
of rows in column d in 3 different sheets. Number of rows in column a
has to equal b as well. I'm not sure if this helps or not but thanks
again for responding.


--
speary
------------------------------------------------------------------------
speary's Profile:
http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

Speary, I think part of the issue, is im not really sure what you are
explaining. In Excel all columns are equally laid out, so column B will
always have the same amount of rows as column D, unless you merge cells.

Do you mean that there are 20 rows worth of data in column B? So that B1:B20
have information in each cell? If thats the case do you want that exact
information in Column D? Perhaps show us an example of what you are trying to
do.

"speary" wrote:


It still seems to set a column length un equal to column 'b'. Is there a
place that I should add the formula to so that it sets the column length
when i first enter in my infor to VB..i tired running it after i had
completed my previous macro but it still had "0"'s going all the way to
the bottom of the spreadsheet b/c i must have copied the formula down
the entire column. How would I copy it just to the end of the number of
rows that contain information. for example:

If column b has 20 rows....so would column d


but in sheet 2

column b has 30 rows....so therefor column d has the same number


--
speary
------------------------------------------------------------------------
speary's Profile: http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

it seems to be working now...i hope. I definately wasn't explaining it
right, i have minimal computer knowledge and tend to confuse terms or
use them interchangable. sorry for the confusion but thanks for the
suggestions they got me on the right track.

Stephen Peary


--
speary
------------------------------------------------------------------------
speary's Profile: http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

So, for the archives, what is the correct solution. Post your code.

--
Don Guillett
SalesAid Software

"speary" wrote in
message ...

it seems to be working now...i hope. I definately wasn't explaining it
right, i have minimal computer knowledge and tend to confuse terms or
use them interchangable. sorry for the confusion but thanks for the
suggestions they got me on the right track.

Stephen Peary


--
speary
------------------------------------------------------------------------
speary's Profile:
http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878

set a column length
 

Columns("F:F").Select
Selection.INSERT Shift:=xlToRight

Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Columns("D:D").Select
Selection.delete Shift:=xlToLeft




Like I said this seemed to work for me to make it so I had an equa
number or data cells in each column. Before I had zero's going all th
to the last cell on the spreadsheet in column e after the data ended i
in column d. before I couldnt get the

Range(selection,selection.end(x1down)).select line to work it kept o
giving me error messages.

That was basically what i was trying to ask in my original question wh
that error message kept coming up

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

set a column length
 

try this instead to copy E values to F
Sub copyvaluestonextcol()
With Columns("E")
.Copy
.Insert Shift:=xlToRight
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End With
End Sub


--
Don Guillett
SalesAid Software

"speary" wrote in
message ...

Columns("F:F").Select
Selection.INSERT Shift:=xlToRight

Range("E1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("F1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("D:D").Select
Application.CutCopyMode = False
Selection.delete Shift:=xlToLeft
Columns("D:D").Select
Selection.delete Shift:=xlToLeft




Like I said this seemed to work for me to make it so I had an equal
number or data cells in each column. Before I had zero's going all the
to the last cell on the spreadsheet in column e after the data ended in
in column d. before I couldnt get the

Range(selection,selection.end(x1down)).select line to work it kept on
giving me error messages.

That was basically what i was trying to ask in my original question why
that error message kept coming up.


--
speary
------------------------------------------------------------------------
speary's Profile:
http://www.excelforum.com/member.php...o&userid=24959
View this thread: http://www.excelforum.com/showthread...hreadid=384878