set a column length

[speary]

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.


Range("D2").Select
Worksheets("thru 1-1-2005").Activate
Range("B2",Range("B2").End(x1down)).Select
Selection.Copy
Selection.offset(rowOffset:=0,columnOffset:=2).Activate
ActiveSheet.Paste

Stephen Peary

[Don Guillett]

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
[email protected]
"speary" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[speary]

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

[havocdragon]

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
>
>

[speary]

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.

[Don Guillett]

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
[email protected]
"speary" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[havocdragon]

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
>
>

[speary]

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

[Don Guillett]

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

--
Don Guillett
SalesAid Software
[email protected]
"speary" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[speary]

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.

[Don Guillett]

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
[email protected]
"speary" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>