MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Some Questions from Beginner

  Asked By: Leona    Date: Jan 03    Category: MS Office    Views: 1194

As a beginner, I have some questions.

Last Cell

Suppose that I have column A, last active cell is A250 , but previous cells
A115, A10, A77 are an empty cells.

How can VBA recognize A250 as a last cell ? is there any standard function ?

I will use this function/method as a constraint of my iteration base on last
cell input by user (to cut iteration time).

Find method

What is the code to find data and the address from a single column?

For instance: I want to find date: #March 3,2005” from column A.

Output Desired: 3 data found in A1, A10, A120

String Operation

If I have string data: “ Hello World ”,

What is the string function to delete space?

So the output becomes : “HelloWorld” (without left, center and right space). I
can delete left and right space using trim function, but I don’t know how to
delete center space.

And how to delete certain character from the string to get output : “Heo Word”
(delete “l” character from the original string : “Hello World”)



2 Answers Found

Answer #1    Answered By: Xavier Thompson     Answered On: Jan 03

the last cell  thing was my first question to the list as well.

Last Cell -

LastCell = Worksheets("<WorksheetNameGoesHere").Cells.SpecialCells

String Operation you're looking for is:

substitute(string, old bit, new bit)

substitute(" hello world  ", " ", "")

Not sure about the best way to find, it's prob some kind of lookup
but it's not something I use much myself.

Answer #2    Answered By: Damon Perez     Answered On: Jan 03

one more comment on the last cell  question. Actually Excel is also treating
cells which are deleted with DEL key or ClearContent as part of its data  range.
This may You can explore the data range with the UsedRange command.

FRow = ActiveSheet.UsedRange.Row
FCol = ActiveSheet.UsedRange.Column
LRow = FRow + ActiveSheet.UsedRange.Rows.Count - 1
LCol = FCol + ActiveSheet.UsedRange.Columns.Count - 1

FRow gives you the first row, FCol the first column. It may be that your used
range does not start in row 1 and or column  1. That's why I use the formula for
LRow and LCol to adjust that.

As said, when deleting some rows or columns, you still may see the last cell
outside of your range. I normally use the command line
FRow = ActiveSheet.UsedRange.Row
even if I don't need it to adjusts the UsedRange to deleted rows/columns.

I'm not sure if you also want to delete  the empty  lines in A115 etc. if so, here
is some code  you could use:
FRow = ActiveSheet.UsedRange.Row
Row = FRow
While Row < FRow + ActiveSheet.UsedRange.Rows.Count - 1
If Application.WorksheetFunction.CountBlank(Rows(Row)) = 256 Then
n = ActiveSheet.UsedRange.Rows.Count
Row = Row + 1
End If

This code also works if your table does not start in row 1. I use FRow +
ActiveSheet.UsedRange.Rows.Count - 1 in the While command line, because this
re-adjusts the data range whenever deleting a row. Thus, I do not have to loop
through a fixed length of a table. Please note that this routine takes long time
if you have a large table. You may want to disable screen updating.

Didn't find what you were looking for? Find more on Some Questions from Beginner Or get search suggestion and latest updates.