Visual Basic :: Selecting A Range {until Empty Cell}

Selecting Cell Range By Clicking It
Let's say I'm making a chart. At some point I'm asked for the range of data to use. I can either type in a cell range, e.g. "=Sheet1!$A$5:$B$10", or I can click that little icon to the right of the text box and then simply click on the cell range I want. Easy peasy!

I want my macro to do exactly this. I want my user to start running the macro, and be asked in a dialog box for a cell reference, at which point they can just click on the cell they want to use (which can be on a different sheet).

Can this be done, and if so, could someone point me in the right direction? Thanks a lot,

MS Excel - Selecting A Range Using The Last Cell
Hi! Can you help with this problem... it may seem really simple, but I'm new to VB so it's proving to be a little tricky (for me!). Basically the problem is this; using code I've written a VB macro that will present data in a more "visually friendly" format. This is done in Excel. The only thing I'm having problems with is border style. The thing is the macro is to be used on many different sources of data and there will never always be the same number of records (rows) in the spreadsheet each time. Therefore, what I'm trying to do is select all of the cells up to and including the last cell and then format the cells so that they have a border. At the moment I've had to define a range and then afterwards I manually delete the excess cells. Is this possible?

If you can help, I'd be very grateful... not to mention relieved in not having to manually delete cells I don't need.

Thanks.

Selecting Top (bottom) Left (right) Cell Of Any Range W/o Using .end()
Hi guys,

I have a range consisting of at least one cell (no MultiAreaRange), and now I want to select one of the edge-cells, let's say the top left cell. How can I do that without using the .end()-thing. I was thinking about a solution using the range.address, but I am not getting anywhere...

But as I know you guys, you'll present me a solution in no time

Thanks in Advance!

Insert Cell Format & Get Cell Value Without Selecting Worksheet
Hello
       I am trying to tidy up a workbook. I have several macro's, one of them insert a new line with different cell formats in to different worksheets and most of the others get a cell values for creating reports.

The only way I know of inserting / getting a cell value is to select the worksheet and run the macro

        Sheets("Works Schedule").Select

        ' Run code

This way works but is their a way of running the macro with out selecting the worksheet to stop the workbook flickering between each sheet whiles the macro is running

Thanks
          Tom

Copying Dynamic Range Based On Cell Outside Of Range
i am copying a dynamic range of cells from twelve different worksheets. i need to select a range that starts of g10 on every sheet, but ends differently on every sheet. the range ends depending on column A in the last row of each range, the word 'total'. i would like to select the range in column g10 down to the cell corresponding with 'total' in column a in column g. For example, if there is 'total' is in a56, i want to select and copy g10-g56. If on another sheet 'total' is in a68, i want to select and copy g10-g68. there is information below the range. i want to ignore the data below altogther. here is the code that i have now for the first sheet. it grabs G10 through the last cell in column g. any help would be appreciated. thanks

Code:
Sheets("January 2005").Select
Range(Range("G10"), Cells(Rows.Count, Columns("G").Column).End(xlUp)) _
Copy Worksheets("SALES REPORT").Range("a2")

Determining Wether Current Cell/range Within A Specified Range
Hi,
I am trying to write a macro for excel spreadsheet.
I am trying to find some function or method to determine wether a particular cell is currently within the specified range or not.

A simple example to illustrate question:

Dim Action_Range as Range
Set Action_Range = Range("A1:H:10")

Dim Current_Range as Range
Set Current_Range = ActiveCell

If Current_Range is part of the Action Range then
'do something
Else
'ignore
End if

Can somebody give some suggestions?
Thanks

Automatically Calculate Row Or Cell Range Triggered By Cell Entry
Due to the slow speed of calculation (due to its size) of my excel spreadsheet I have turned calculation to manual.

I would like to trigger automatically a row to recalculate (or perhaps part of the row) once a cell entry has changed.

Can anyone help.

Selecting A Cell Based On Where The Active Cell Is
Firstly. Hello gurus, developers, lurkers and other bobs. It is a pleasure to find this place and meet you all.

Secondly. Here is the problemette that I am looking for assistance on.

I have been given an Excel spreadsheet to maintain and improve. What I am looking for is to be able to copy data from one worksheet to another with the following requirements. The data that I select is in worksheet 1, columns A and C which I then want to copy to worksheet 2. The process will be dependent on where the active cell is in worksheet 1, which could be anywhere within the range D3 to L1000. (I hope this makes sense)

Up until being given this spreadsheet to maintain I have never used any form of VB before. I have tried a multitude of searches here and on a few other sites and the results of those indicted using Range(ActiveCell, ActiveCell.End(xlToLeft)).Select in some way but so far I have been unable to make it function as required so I am open to suggestions.

Regards

Youngnutter

Cannot Distinguish An Empty Cell From A Cell Having A Value Of 0
guys i need some help on this one,
i am comparing two cells in excel, however
if the value of the first cell is 0 (zero) and
the other cell has no value the resulting
value is true...
meaning comparing 0 and an empty cell evaluates to true,
how is this possible, how can i make this expression
to evaluate to false???

hanks.

Cell Value Based On Cell Range
I am trying to have the value of cell A multiplied by a certain amount in Cell B, based on a value range of cell C. Any help would be appreciated.

Ex:

Cell A = 25
Cell B = the value of Cell A multiplied by 10(if the value of Cell C is less than 18), multiplied by 15(if the value of Cell C is greater than or equal to 18), or multiplied by 20(if the value of Cell C is is greater than 20)
Cell C = percentage value (IE. 20.00%)

Last Empty Cell
What I want to do is to simply take the data from the last cell, copy it and then paste it 5 cells down. If I use the code below the computer hangs every time. What am I doing wrong?

Code:

Cells(Rows.Count, 1).End(xlUp).Offset(0, 0).Row

Range(Cells).copy

Range(cells + 5).Paste

Next Empty Cell
Hey guys and gals...

should be simply but not working too well...

I have UserForm1 with 5 info boxes accross

that is the same 12 times down the form..

All I am trying to do is write info from the UserForm (on button click) to the next empty cell from a starting row...

Get A Next Empty Cell
I have a prog using which I can open an existing Excel file and then send the date from it to new created Excel file. But here I have a problem on passing to another cell.
I mean how can I go to the next empty Row after I entered the date?
Let's see an example:
The first time when I click on SendButton the date will be placed on new created file in A1,B1,C1,D1,....est. the next time I will send the date I need it to go to A2,B2,C2,D2,....est.

How can I learn the next empty Cell

Private Sub SendButton_Click()

Like what steps I need to go here to learn
the next empty row

ws.Cells(1, 1) = txtName.Text
ws.Cells(1, 2) = txtAmount.Text

End Sub

Any idea appreciated!

Check Range Is Empty / Null
hi,
searched the forum for 2 hours, but i'm stuck.
i want to check a range if its empty, if it is: go to next range, else do other stuff.
the first range it checks is empty, i'm pretty sure, but it keeps trowing me into True(not empty). tried isnull, isempty, "".
what am i doning wrong??
part of the code:

Code:
If not IsNull(Worksheets(ListBox1.Value).Range(Cells(b, onpipe), Cells
(e, onpipe))) Then
onp = True
Else
onp = False
Exit For
end-if

thanx in advance for any help!

Help! Copy Range & Paste To Last Empty Row
I need to copy a range in workbook1, then open workbook2 and paste data in last empty row. Any ideas?

I have the following...

Private Sub CommandButton1_Click()

'..ignore this statement
If Application.WorksheetFunction.CountA(Range("E7:J7")) < _
Range("E7:J7").Cells.Count Then
MsgBox "Complete ALL Fields"

'..here's where I need help

Else
Range("C7:J7").Select
Selection.Copy
Workbooks.Open Filename:= _
("C:My DocumentsWorkbook2.xls")
??????????
Selection.Paste '..<== does this work??
Application.CutCopyMode = False
End If
End Sub

TIA
- Larry -

Best Way To Determine If A Range Of Cells Is Empty
I would like to determine if the cells in the following range are empty:

Range("A6:A10,C6:C10")

Obviously I could use two loops (one nested on another) that sets a boolean = IsEmpty(cell) and kick out of the loop if the boolean becomes true, but I'm sure there is a better way. And if there is, you guys would know it.

Searching For Empty Cells Within A Range
Hi

My worksheet currently uses some code for an invoice type page in which the user presses a button which triggers the macro to enter some data into the cell.

This is the code in which I've used to look for the next empty cell:

Sub cell_photomove()
Sheets("Invoice").Activate
Range("C18").Select
Do Until ActiveCell.Row = 65536
Selection.End(xlDown).Select
Loop
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "Item"
End Sub

This worked great until I noticed that when I entered some data in cells outside of the given range, then the next entry goes just right below the newly added data.

E.g. I pressed the button and the macro types in "Item" in cell C18. I only want such entries to go into cells C18-C25. However, I put something else into C30, and the next data entry goes into C31.

Is there any other code which looks for another empty cell within a range?

Thanks,
Nyra

Selecting A Range
Hello,

I am using VB to copy a formula which is at the bottom of a range and copy it all the way up to the top of the range. However the name of the month is at the top of the range, so i just want copy the formula to the top excluding the month cell.

I am using Range(Selection, Selection.End(xlUp)).Select, which then also includes the month, which I would like to exclude from the selection.

Is there someway I can exclude the month cell from the selection?

Cheers

Selecting A Range
Visual Basic 6.0 with Excel.

Seem to be having a brain cramp today.

I have a spreadsheet that has data from Cell A9 to Cell E73.

Cell B9 to cell B33 contains the number 14.
Cell B34 to cell B73 contains the number 17.

I want to separate the rows with the 14's from the rows with the 17's.

I know I can select the first row of the data with the following:

Code:
Range("A9").Select
Range(Selection, Selection.End(xlToRight)).Select
The second line of the above code is equivalent to holding down Shift, ENd and Down Arrow Keys.

To select the next row I know I have to hold down the Shift and Down Arrow keys, but for the life of me I don't know what the vb code is.

Please help.

Thanks

P.S. Is there any way to attach .xls files to new threads?

Selecting A Range
I have this macro, where I want a certain range of cells to be underlined . I have this code but it seems to give me a mistake. If anyone could tell me what's wrong with it. I think Range("Ai:Ji").Select is where my mistake is. Here is part of my code:

while true
Dim i As Integer
i = 5
Range("Ai:Ji").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDot
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlDot
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlDot
.Weight = xlHairline
.ColorIndex = xlAutomatic
End With
i = i + 1
wend

Thank you very much for your help. I greately apreciate it.

VBA Sub To Delete A Row If A Cell Is Empty
Hello Iam a newbie VBA user.
I have a spreadheet with several rows. Some of teh cells within the rows are blank. I am trying to write a subroutine that will look for a specif cell in a row (say B2) and if it is blank delete the entire row.

The code below will work if a ROW is empty but I am trying to refine it such that I select a range of Cells and if the cell is empty delete the entire row

Sub DeleteEmptyRows()
LastRow = ActiveSheet.UsedRange.Rows.Count
Application.ScreenUpdating = False
For r = LastRow To 1 Step -1
If Application.WorksheetFunction.CountA(Rows(r)) = "" _
Then Rows(r).Delete
Next r
End Sub

Any suggestions?

Excel Help - First Empty Cell
Hi,

I would like to select the first empty cell in a column and then add text to it from a textbox on the userform. What I use is

Code:
Sheet2.Range("B1").End(xlDown).Offset(1, 0).Value = TextBox1.Text
and that works fine,

but then when I try

Code:
Sheet2.Range("J2").End(xlDown).Offset(1, 0).Value = Sheet1.Cells(1, 4).Value
I get the error

'Run-time error '1004':
Application-defined or object-defined error'

what am I doing wrong?

Thanks,

Jim

Finding The Last Empty Cell
I'm struggling to find an empty cell. I'm using:

Code:
intRow = Cells.Find(What:=IsEmpty(),LookIn:=xlValues,SearchOrder:=xlByRows)
I need to know what to put in the IsEmpty bit.

Any ideas?

Fill Next Empty Cell
Hello Everyone,

I have read alot of posts on this topic but I couldnt find one that worked for me, hopefully someone can help me out.

I have a userform in MS Excel in which when a user presses the command button it will fill in data on my WorkSheet1. The data that will be entered is Company Name, Customer Name, Address, Address2, City-State-Zip.

If every field had data in it when the user hits the command button then it would look something like this below on the worksheet:

Cell ("C11") = Company Name
Cell ("C12") = Customer Name
Cell ("C13") = Address
Cell ("C14") = Address2
Cell ("C15") = City, State Zip

However sometimes the Address2 field (which mainly has P.O. Boxes) is empty cuz not every company has a P.O. Box.

When this is the case I would like all the data below this to shift up and fill in the void for that empty field. Same goes with any other field that does not contain any values when the user hits the command button.

Any help is greatly appreciated,
Kind Regards

How To Determine Is A Cell Is Empty?
Hi Everybody

Since the function IsBlank() doesn't work with VBA, is the only way to test whether or not a cell is emtpy is to either of these (shown below) or is there is a built-in Excel function in VBA equivalent to IsBlank().

Code:
'Assume that cell(A1) on the Sheeet1 is blank - nothing has been entered in it yet.

Sub testing()
Dim myrange As Range
Set myrange = Worksheets("Sheet1").Cells(1, 1)
Dim test1 As Variant
test1 = myrange.Value 'test1 returns Empty
Dim test2 As Boolean
test2 = IsEmpty(Worksheets("Sheet1").Cells(1, 1).Value) 'test2 returns True
End Sub

Best regards

Deepak Agarwal

To Select An Empty Cell
In order to select a value before a blank cell, I get this command from other topic:

myvariable = Range("C5").End(xlDown).Row

unfortunately if the cells contain a formula like this:

=IF($C55="";"";........)

the above command not recognize no empty cell until that do not end the formulas introduced

In few word I need to modificate the command, inserting a control if cell is empty, or something like this, and not if is blank.
Someone could write to me this simply (I hope) solution ?
Thanks to everybody.

Excel Cell Value Is Not Empty
Hello,

Sometimes I do not understand simple things but maybe somebody else. I'am testing a cell value but I do not understand why the if statement is true:

if (IsNumeric(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) and _
(cint(trim(wshSubnet.Cells(i, wshSubnet.Range("DBB_CB_NAME"& i).Column))) > iCountInstal )) Then

Now: the value of the Cell is 2; iCountInstal = 2
DBB_CB_NAME"& i is a cell name

What I do not understand how can this be True? Because in my opinion the the value of the cell is 2 and is not bigger than iCountInstal which is 2 as well. The same problem do I have when the value of iCountInstal = 1
I think I make somewhere a stupid mistake but I don't see where

Must I do a type conversion first?

Nico

Empty Cell Verify...
Need to verify that a cell contains no text or value before a VB routine over-wrights it.

I need a little help here, most of the folks that are acquainted with me say that I’m beyond help. All of the cells contain formatting, but none contain text or values on the test sheet, every time the error trap routine (If Not ("C" & RW) = "" Then GoSub FullRoutine) runs it tells me that the cell contains data but isblank() returns true. This is way beyond my comprehenson, haven’t scrached code in a lot of years, usually just record it and hope for the best. But need a little something more here.

Sheets("new").Select
Range("A31:J36,B33,B34,A36").Select
Range("A36").Activate
Selection.ClearContents
Range("A27").Activate
RW = Range("A27") + 1
Range("A36") = RW
Sheets("2005").Select
Range("B" & RW).Select
Range("B" & RW).Activate
If Not ("B" & RW) =””Then GoSub FullRoutine
Range("C" & RW).Select
Range("C" & RW).Activate
If Not ("C" & RW) = "" Then GoSub FullRoutine
Range("A27:I27").Select
Selection.Copy
Sheets("2005").Select
Range("A" & RW).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Stop
FullRoutine:
Sheets("new").Select
Range("A33").Select
ActiveCell.FormulaR1C1 = "STOP"
Range("B33").Select
ActiveCell.FormulaR1C1 = "Are You Sure Someone's not in this unit?"
Range("B35").Select
End
End Sub

Paste To An Empty Cell
My question must have been answered before... but I just can't find the thread...

how can VB paste into an empty excel cell, without producing the "paste method out of range" error?

-jb

How To Avoid Empty Cell?
Hello, Everyone,
If there is some empty cell in 18 column, it won't return the last Row number. Is there a way to solve it?
Thank you very much!
Charlie

Code:
nLastRow = xlApp.Worksheets("sheet1").Cells(65536, 18).End(xlUp).Row

Detect Empty Cell
I'm writing a macro ( actually 2 macros, one for character, one for numeric ) that allows the user to override an exsiting value calulated elsewhere in the spreadsheet. As a first step, I want to detect when/whether the user has entered a value or not.

I thought that something like

mycheck = IsEmpty(Override)
If mycheck = -1 ( or NULL ) Then .....

would tell me that when the user DID NOT enter something ( or had deleted what he did enter ). However, even when the cell is empty ( and confirmed by ISBLANK in Excel ), I can't detect when the cell is empty.

hope this isn't a really stupid queston .

thanks in advance for your help

regards
woody

Empty Cell Test
Guys,
How would you test a column for a empty cell, then once you have found your empty cell, mark that as being empty then go back up one and select from top of the column to the variable defined bottom of that column. Any help would be appreciated. Thanks!

Empty Cell In Graph
Hello,
Facts:
Column A are X axe of a graph. Column B is the Y axe of the graph.
If the cell B3 equal zero or some texte, the graph will show a point at zero. But if B3 is empty, the graph will not show the point at all.
Question?
My cell B3 contain a formula, which return "" if false. Excel recognize "" as empty cell, but the graph show a point at zero! I would like either:
-to find a way that the graph do not show the point when the cell got "" as value.
-to find a way in the formula to return an empty cell (carriage return?)
I have already tried to play with the graph options with no luck. I am trying not using VBA.
Thanks in advance,
Chib

How To Emulate A Really EMPTY Cell?
Hi folks,

I can't find a solution for my problem. Excel treates empty cells very special and I want to have this behavior for cells containing following function:

Public Function NumOnly(r As Range) As Variant
' r referes to cells containing numbers of any kind -> return this number
' otherwise make the cell appear empty
' (event to Excel: e.g. Charts or AVERAGE)
Dim c As Range ' For Each c

Application.Volatile ' always recalculate

For Each c In r.Cells
Select Case VarType(c.Value)
Case vbInteger, vbLong, vbSingle, vbDouble ' numbers only
NumOnly = c.Value
Case Else ' otherwise empty
' NumOnly = CVErr(xlErrNA) ' #NV doesn't do what I want
' Set c.Value = Empty ' this in turn doesn't work
End Select
Next c
End Function

It is not enough to return "" because charts would just draw a value of 0 and AVERAGE would sum up this cell, too. The porblem is that the cell can't be empty as such, since it contains a formula (e.g. "=NumOnly($A$1)"). But how can I make Excel behave like it was empt? With the #NV-solution I could get by (sort of) but line charts wouldn't leave a gap where the empty cell should be and AVERAGE would return #NV, too.

Please help

AndyR

Giving A Cell An 'empty' Value
I want to include an IF statement in one of my cells and I want to have an 'empty' value, that is, the same as if i had just pressed delete to wipe the contents of that cell. How can I integrate this into my EXCEL formula?
Thanks,
Rob

Find First Empty Cell
I am using VB to open a worksheet and find a value within a cell. From there I need to read every cell until I get to the first blank cell. If I know the column and row to start from in question how can I find the first row in that column that is empty.

How To Determine If A Cell Is Empty
I'm attempting to create a macro that will delete an entire row if the gift
amount is less than $99 and the comment field is blank.

'Delete row for all gifts under $100 and comment field is empty
giftcount = 1
Set xRng = Selection
Range("A2").Select
For giftcounter = 2 To FinalRow Step 1
If Cells(giftcount, 89).Value <= 99 Then
If Cells(giftcount, 96) = "" Then
xRng.Cells(giftcount).EntireRow.Delete
Else
giftcount = giftcount + 1
End If
Else
giftcount = giftcount + 1
End If
Next giftcounter

Thanks,
Dorie

********************************************
"Success is to be measured not so much by the position
that one has reached in life as by the obstacles which he
has overcome while trying to succeed.
-- Booker T. Washington

Selecting A Range Of Dates
I am using Access 2000.

I have a calendar control on a form. I want to be able to select a range of dates and then them dates to be wrote to a table.

For example if I choose the range between 01/01/2003 to 04/01/2003 I want them dates, and the dates in between to be wrote to a table, so 01/01/2003, 02/01/2003, 03/01/2003, 04/01/2003 would be wrote to a table.

I am able to select the start and end date but it is not selecting all of them.

Anyone got any ideas?

Thanks

Selecting A Range For A Chart
I'm creating a chart using in Excel using VB, however the data range varies in number of rows but always starts at cell "A1".

I believe I can select the range using: Selection.CurrentRegion.Select

But how do I store that in a variable and then pass it to the code to create the chart. When I store in a variable and print I only see "true". I need something like A1:C19

Thanks in advance

Selecting A Range Of Cells Bar One
Hello all,

This is my first post to this forum since joining so please be gentle with me.

I have what I believe a simple problem whereby I wish to select all cells in a 3 high set of consecutive rows, except for the last column of cells

i.e.

****A B C D
=========
1| C D E G
2| F H K K
3| H M Q P
4| Z T Y U

I wish to select A2 to C4

If I position the cell pointer at cell A2, I use the following code to select to the last column, but want to move the selected area back by one column

Range(Selection, ActiveCell.Offset(2, 0)).Select '// down two cells
Range(Selection, Selection.End(xlToRight)).Select '// select to end right

If anyone could assist I would be most grateful.

Best regards and thanks in advance,

KevinO

Selecting A Range In Excel
I have this really long macro that looks like this:

Code:
Sub Macro1(lquota As Long)
Workbooks.Add
Sheets("Sheet1").Select
Columns("B:B").ColumnWidth = 8
Columns("C:C").ColumnWidth = 25
Columns("D:D").ColumnWidth = 12
Range("B1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "DATE"
Range("C1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "RESP. #"
Range("D1").Select
Selection.Font.Bold = True
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveCell.FormulaR1C1 = "VFY.IN"
Range("A1:D54").Select 'this part here
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
Range("a1").Select
Dim i As Long
i = 2
Dim temp As String
For i = 2 To lquota
Cells((ActiveCell.Row + 1), ActiveCell.Column).Select
ActiveCell.FormulaR1C1 = (i - 1)
Next
ActiveWorkbook.SaveAs FileName:="c: emp.rcd", FileFormat:= _
xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:=False _
, CreateBackup:=False
End Sub
the part that says
Range("A154").Select
I want to make variable from A1 to D?
how can I do this. Thanks
Rob

Selecting A Range From Different Columns
Hi,
I would like to select a range of cells from two separated columns. For example a range from column A and a range from column D.

My code currently selects a range of cells from column A and B and looks like...

Sub Macro1()

Range("A1").Select
ActiveSheet.Range("A1").End(xlDown).Offset(1, 0).Select
ActiveCell.Offset(-1, 0).Activate
Selection.EntireRow.Delete
Range("A1").Select
Do Until ActiveCell.Value = 0
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Offset(-1, 0).Select
Range(Selection, Cells(2)).Select
End Sub

How might I modify the code to select from columns A and D?

Any help would be greatly appreciated.
Thanks

Selecting Range With Variables
I am trying to use variables to select the columns in a Range. The below code gives me and error message. I also want to merge the cells.

lastDisplayCol = ColumnLetter(i - 1)

Worksheets("Display").Activate
Range("c1", lastDisplayCol & "1").Select
With Selection
.MergeCells = True
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
End With

Thanks,

Brandon

Selecting A Range Of Cells
Hi,
As a complete beginner with VB, I'm having trouble with the simplest of tasks. I'm in the process of designing a simple spreadsheet for my IT course. I'm required to create several macros in VB code. My spreadsheet contains a table with 8 columns and a variable no of rows. The No of rows depends on how many members of staff I have. I am recording their work hours over a weekly period. So I've got headings in the Columns of "Mechanic's Name", "Serial No", "Mon thru Fri", & "Weekly Total" which makes 8 columns. The bottom row of the table is for Daily Totals for each mechanic. I have already created a macro for inserting the extra row every time I want to add a new member of staff. What I am trying to do now is write a macro in VB to select all the data which is added for all mechanics without the totals and then create and insert a line chart. What I've done so far, hopefully gets my active cell to the last mechanic in the list. This is where I am stuck. I need to be able to select the correct range of data remembering this will always vary. Once I have done this I think I will be able to continue. At the moment I can't see the forest for the trees. So far the code gets me to last mechanic inserted in column one. After this I can program to select cells along that row but need to include all the data in the rows above it is selected for the chart. I'm getting confused when using "index" & "loop". Here is my code so far.

Sub CreateChart()
'Macro created by Carl Strevett to create and insert a chart showing Mechanic's hours
'
' Set variable
Let index1 = 1
'Go to Home cell "A1"
ActiveSheet.Range(Cells(1, 1), Cells(1, 1)).Select
'
'Find last Mechanic
Do While ActiveCell <> "Daily Total"
Let index1 = index1 + 1
ActiveSheet.Range(Cells(index1, 1), Cells(index1, 1)).Select
Loop
Let index1 = index1 - 1
ActiveSheet.Range(Cells(index1, 1), Cells(index1, 1)).Select
Let index1 = index1
ActiveSheet.Range(Cells(index1, 1), Cells(index1, 7)).Select

End Sub

Would appreciate any help with this. Cheers, Carl

Selecting A Range Of Date
VB6 Enterprise Edition

I have an Access database - the table is called TRACKING, is described:

Code:
dbTrackPercent - currency
dbTrackPONumber - Text(10)
dbTrackPODate - Text(10)
Note: I can not change the dbTrackPODate field to type-Date

I need to SELECT records within a RANGE of dbTrackPODate.

I have tried the following:

Code:
Public StartDate As String
Public EndDate As String
StartDate and EndDate are populated via an InputBox

Code:
SQL = "Select * from Tracking where dbTrackPODate "
SQL = SQL & "between '" & StartDate & "' and '"
SQL = SQL & EndDate & "' order by dbTrackPODate;"
I have also tried:

Code:
SQL = "Select * from Tracking where dbTrackPODate "
SQL = SQL & "dbTrackPODate >= '" & StartDate & "' and "
SQL = SQL & "dbTrackPODate <= '"& "' EndDate & "order by dbTrackPODate;"
Via the Input box, I entered:
12/01/2005 (StartDate)
12/31/2005(EndDate)
Both code examples gave me dates for December 2004 and 2005. I just wanted 2005.

Any suggestions on how I can code this to get the correct results?

Selecting Range Of Records
I'm trying to select the first 5 records in my Access DB and from that selection find the Max and Min of the field 'Score Total'. My problem is I need to sort the table by the 'Date Played' field first but I get the following below error when I use the code below.

Can anyone help me out??

"You tried to execute a query that does not include the specified expression 'Date Played' as part of an aggregate function"

Code:
sSQL = "Select Top 5 Max([Score Total]) AS MaxScore, Min([Score Total]) AS MinScore FROM tblScores WHERE CourseName = '" & CourseName & "' ORDER BY [Date Played] DESC"
EDIT - *SOLVED*
This is how I solved it

Code:
sSQL = "Select Top 5 Max([Score Total]) AS MaxScore, Min([Score Total]) AS MinScore FROM
(Select Top 5 * From tblScores WHERE Name = '" & CourseName & "' ORDER BY [Date Played] DESC)"

Selecting A Range Of Values
i am currently making a calculator with a combobox which has about 100 set values. I am wanting to know how do i select a particular ranges of these values using an IF.. THEN.. statemet.

Private Sub Command1_Click()

If Combo1.ItemData(Combo1.ListIndex) = 2411 Then
Label13.BackColor = vbGreen
Label14.BackColor = vbYellow
Label15.BackColor = vbRed
Label16.BackColor = vbRed
End If

If Combo1.ItemData(Combo1.ListIndex) >= 2746 < 13363 Then
Label13.BackColor = vbGreen
Label14.BackColor = vbGreen
Label15.BackColor = vbRed
Label16.BackColor = vbRed
End If

end sub

When i select the value for lets say 2100 then the label13 and 14 are both green, and i assume this is because of the part < 13363 in the later IF THEN, how can i set it so only the specified range is the condition?

Selecting A Range {until Empty Cell}

HiI'm trying to write code that will select a range of cells (in column A) but will stop selecting once an empty cell is detected. I'm not sure if this code should include a do-until loop?I would greatly appreciate any help.Thanks

Hi

I'm trying to write code that will select a range of cells (in column A) but will stop selecting once an empty cell is detected.

I'm not sure if this code should include a do-until loop?
I would greatly appreciate any help.
Thanks