Help novice

J

Jac

Hi i want to select a range of cell with

Sheets("test").Select
Range("A1:B21").Select

Instead of using A1 and B21 i would like to use a variable that would start
at 1

Dim cpt as integer
cpt = 1
Range("A & cpt:B & cpt +20")

I dont know how to write this

Can someone help me

Thanks
 
D

dustinbrearton via OfficeKB.com

I am not sure when you are wanting it to happen but the below code will work.
I suppose later in the code you are going to change the value of CPT?

Dim CPT As Long

CPT = 1

Range("A" & CPT & ":B" & CPT + 20).Select
 
T

Tom Hutchins

Try

Range("A" & cpt & ":B" & cpt + 20).Select

You have to put the variable outside the set of double quotes, or Excel
thinks it is part of the string. Same with the +20.

Hope this helps,

Hutch
 
D

Don Guillett

This will do. Be sure to correct for word wrap, if necessary.

Sub gotovariable()
dim fr as integer
dim lr as long
fr = 1
lr=20
'one line below
Application.Goto Sheets("sheet10").Range("b" & fr & ":b" & lr)
'one line above
End Sub
 
R

Rick Rothstein \(MVP - VB\)

Anything between quote marks is considered pure text (the characters that
make it up, not a value that a variable whose name is the same as those
characters would hold, so putting cpt inside them won't get you what you are
after... the variable's name must be concatenated onto the text.

Range("A" & cpt & ":B" & (cpt + 20)).Select

Rick
 
C

Chip Pearson

Try something like:

Dim R As Long
Dim C As Long
For R = 1 To 10
For C = 2 To 5
ActiveSheet.Cells(R, C).Select
Next C
Next R

This will sequentially select the cells in columns 2 to 5 in rows 1 to 10.


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
D

Don Guillett

OR

Sub gotovariable()
fr = 1
lr = 21
mc = "b"
With Sheets("sheet10")
Application.Goto .Range(.Cells(fr, mc), .Cells(lr, mc))
End With
End Sub
 
D

Dan R.

Are you trying to loop through the range maybe?

Set rng = Sheets("test").Range("A1:B21")
For Each cell In rng
' cell.select
cell.Value = cell.Address
Next cell
 
J

Jac

Tom or any other one :)

How can i find the last cell in a sheet that has somethin written in it ?

Thanks !
 
R

Rick Rothstein \(MVP - VB\)

The last cell where? Do you mean the last row containing data? Or the last
column containing data? Or something else? If something else, then you have
to tell us which is the last "cell" between, say, B4 and C3 (assuming they
were the only cells filled in).

Rick
 
J

Jac

I want to retreive the last row

Thanks !

Rick Rothstein (MVP - VB) said:
The last cell where? Do you mean the last row containing data? Or the last
column containing data? Or something else? If something else, then you
have to tell us which is the last "cell" between, say, B4 and C3 (assuming
they were the only cells filled in).

Rick
Click to expand...
 
R

Rick Rothstein \(MVP - VB\)

Give this function a try...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows when
determining the maximum row that is in use; that is, if a hidden row
contains the maximum row, it will be ignored unless the second argument is
set to True. This allows you to get the maximum row for what you see on the
worksheet rather than for what any hidden data would return. I wasn't sure
which would be the most logical default for this second argument, so I chose
not factor in hidden rows (that is, the functions return the maximum row for
only the visible data); if desired, this can be easily changed in the
declaration headers for the function (change the False to True).

Rick
 
J

Jac

Thanks Rick for you help it's working !!!!
Rick Rothstein (MVP - VB) said:
Give this function a try...

Function MaxRowInUse(Optional WS As Worksheet, Optional _
FactorInHiddenRows As Boolean = False) As Long
Dim X As Long
Dim LastRow As Long
If WS Is Nothing Then Set WS = ActiveSheet
With WS
For X = 1 To .UsedRange.Columns.Count
If Not (Not FactorInHiddenRows And Columns(X).Hidden) Then
LastRow = .Cells(.Rows.Count, X).End(xlUp).Row
If LastRow > MaxRowInUse Then MaxRowInUse = LastRow
End If
Next
End With
End Function

Note: If you don't specify a worksheet in the first (optional) argument,
then the active sheet is used. The second optional argument is the
interesting one... it lets you determine whether to include hidden rows
when determining the maximum row that is in use; that is, if a hidden row
contains the maximum row, it will be ignored unless the second argument is
set to True. This allows you to get the maximum row for what you see on
the worksheet rather than for what any hidden data would return. I wasn't
sure which would be the most logical default for this second argument, so
I chose not factor in hidden rows (that is, the functions return the
maximum row for only the visible data); if desired, this can be easily
changed in the declaration headers for the function (change the False to
True).

Rick
Click to expand...
 
Top