Non Numbers Cells
I would like to know if I can create a Macro that will do
a search in column A (from A2 to A65536) and every row
that has anything else than a number, delete the entire
row (not just the cell).
try the following macro:
Dim lastrow As Long
Dim row_index As Long
Application.ScreenUpdating = False
lastrow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
For row_index = lastrow - 1 To 1 Step -1
If not isnumeric(Cells(row_index, 1).Value) then
Application.ScreenUpdating = True
>-----Origi...series of cells in a column #2
thanhyou so vey much
ROLG's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=16066
View this thread: http://www.excelforum.com/showthread.php?threadid=275347
...How to join auto number and letters
I have a form for issuing a letter number for our letter book. In this form
, I have a text box with auto number . what I need is to issue auto number
start with letters. For example: APP&P1, which 1 is the auto number and it is
start with “APP&P”. once the user complete the letter subject, letter receive
and the sender , will receive a control number or letter number.
What about having two fields, where the first field contains the letter
prefix and the second field contains the number? You could display the two
fields in a calculated control combining the data.
"...series objects; finding specific ones in the collection
I asked this before but I'm still not clear on it.
When ever I wish to create a new series, no big deal:
Dim SeriesRandTarget As Series
Set SeriesRandTarget = myChart.SeriesCollection.NewSeries
As long as I have the object, I can use it. If I set the name
attribute as in
SeriesRandTarget .Name = "Hello"
I can collect it later if needed by:
Set myseries = myChart.SeriesCollection("Hello")
But what about this? What is the new series name I just created in
the below example? (The macro recorder has me in this fix!)
myChart.SeriesCo...Cutting a line in a line chart when data series stops
I have a series like so:
And so on - the rest of the year is blank. When I draw a line chart for the
full 12 months the series draws a line up to April, then plummets to zero in
May. I want the line to just stop at April. If I use a series as above and
genuine empty cells for the end of the year this ok, but since my May cell
reads =IF($A11<=$D$17,G11,"") the "" does not seem to get recognised as a
Change your formula to =IF($A11<=$D$17,G11,NA())
"DannyS" <Da...Loop through columns and sum them at bottom
Hi, first time poster, so be kind.
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1...Deleting check numbers
I finally got online updates to match downloaded transactions with my manual
entries. I did this by opening up a completely new file (starting pretty
much from scratch), only way it would work. Now there are 2 new problems.
One, when I "accept", it is replacing check numbers with "check", even though
it keeps my manual payee name. And two, it keeps downloading the same
transactions so I repeatedly have to accept them over and over again after
each update. Any thoughts? Is this all still a part of the same Yodlee
problems, or is there an adjustable setting that ...count number 12-17-07
Is there a way to show a count of records with the same part numbers.
Create a new query in design view.
Add the table with the part numbers.
Click on the Totals button (looks like a "sum of" button from Excel).
Add the [PartNumber] field ... leave the aggregation as "GroupBy".
Add the table's primary key field ... change the aggregation to "Count".
Microsoft Office/Access MVP
"Jeff Klein" <jklein@nospam> wrote in message
> Is there a way to show a c...Merging 2 excel sheets with numbers and words
I started with one sheet (using Excel 2003) but took a copy home (on a flash
drive)to work on and added data (both numbers and "X" 's) - using Excel 2007.
I saved the work done at home so Excel 2003 could "read" it. Then, I did
some work on the copy my computer at work (BIG mistake!!)
So, the problem is: I have some data on my flash drive copy which is not on
the work computer sheet, and vice versa. I want to merge the 2 copies - so I
cannot replace either one because data will be lost.
Options??? Way to do this? (It seems like it will be easier to "Co...How can I add values from several worksheets in a line chart seri.
I have an Excel document with 12 (monthly) worksheets. I want to make a line
chart pulling data in one cell (same cell on each sheet) from each of the
twelve sheets. In other words, Jan F2 + Feb F2 + Mar F2, etc.
Can I do that on the chart's Source Data menu in the Series/VALUES field? If
so, what is the proper language/formula. If not, what are my options? Do I
have to make a total page and pull from it?
Also, is it possible to make the Category (X) axis label be the Worksheet
Name (Jan, Feb, Mar)
Lastly, is there a place that gives sample formulas for someone trying to
brush...put numbers back on a calendar template after removing shading?
n the Event Schedule Planner template, when I remove shading from the
calendar the numbers are removed also. It won't let me put them back. When I
use "no fill" it removes the numbers too.
Exactly what template are you using? What version Publisher?
Mary Sauer MSFT MVP
"dfarnelli" <firstname.lastname@example.org> wrote in message
> n the Event Schedule Planner template, when I remove shading from the...Formula to see if a number is allocated
I need to find out if a number is allocated or free. Basically on sheet1 is
a load of information. in Column G on this sheet is a unique identifier
In sheet2 I have a list of numbers say from 1-100 which correspond to the
identifier numbers in sheet1. Id like a forumla something like:
"If A1 in sheet 2 = any number in column G in sheet1, then print Allocated,
else print free"
At the moment I have: =IF(A1=Sheet1!G:G,"allocated", "free")
But it does not work. What it is doing is If A1 = everything in column G
then say allocated, else say fre...Combining Worksheet Numbers
And old problem revisited. I brought this question to this board about a
year ago and got two spectacular answers from Tom Ogilvy and Ken Wright.
See previous thread ---> http://tinyurl.com/2yleu
I will repeat my question again just in case
Anyway, I have the same problem/question, but this time instead of all of
the worksheets having a static set of values (names), the players will be
changing positions from "season" to "season." Thus, the Start to finish
thing doesn't seem to work.
I am doing a career statistical page for an online soccer simulation. I
want to...Formula for selecting a varying cell reference
I'm trying to tie 2 worksheets together. The first is a data entry sheet,
set up by month. The second is a sheet that I want to pick up monthly data
-- i.e., the most recent month of information entered on the data entry
sheet. Is there a formula I can write in the monthly sheet that will look at
the data entry sheet and go to the last monthly column that has data and
retrieve that? I guess I'm thinking like a flowcharting yes/no process
starting in December -- i.e.,:
Is December blank? Yes -- go to November; No -- use this data -- etc.,
...Why does my mouse not release a range of cells when selected?
When I try to select a range of cells, the mouse will continue to highlight a
range of cells and will not release. I end up having to force Excel to close
and restart. Any help would be greatly appreciated. It does it with most
versions of Excel.
Check if the F8 key was not pressed (you would have the "EXT" mention on the
Excel Status bar), if yes press F8 again.
Apart from that I think this could be a mouse driver problem so to make sure
try and update your mouse driver (esp if you have a wheel mouse).
Apart from that?
"Wolfer50" <Wolfer...Hiding a column of hyperlinks behind a column of numbers
I have two adjacent columns in Excel 2007: one with numbers, one with
hyperlinks. I wish to "place" the column of hyperlinks behind the column of
numbers, so if one clicks on the "number" the hyperlink will open. I know how
to do this to each individual cell one at a time, but the columns have a
couple thousand numbers and hyperlinks. Is there a way I can combine or merge
the columns all at once to do what I want? Thank you for any response.
Are your hyperlinks the result of the
function or are they Inserted hyperlinks??
Gary''s Stud...Selecting Cells
Please help, When starting excel, I click on a cell and
get the cross pointer but when I move the mouse to put a
formula or data into that cell all cells are selected in
the direction of mouse movement. I am unable to de-select
the cells and the only thing I can do is close excel from
the task bar. I have come across this problem once before
But cannot remember how to solve it. Thanks in advance for
any help with this issue.
You sould only get the cross hair if you move the mouse
over the box at the bottom right-hand of the cell. This is
used to enter a fill of a s...Invert selection in List Control
I'm trying to invert selection (i.e. select unselected and unselect
selected items) in a multiple-selection List Control in report mode. I
tried the following
for( int i = 0; i < c_loadedList.GetItemCount(); i++)
if( c_loadedList.GetItemState( i, LVIS_FOCUSED) == LVIS_FOCUSED)
c_loadedList.SetItemState( i, 0, LVIS_FOCUSED); // deselect
c_loadedList.SetItemState( i, LVIS_FOCUSED, LVIS_FOCUSED); // select
but it doesn't seem to work. When I select something and click the
"invert" b...Transation sequence number issue
Happy new year everybody!!
I have a customer with this situation. his invoice must be sequentially he
can't skip a transaction number for his invoices, when a transaction is
aborted a number is skiped. i konw that one is not hard to fix but this is
the main issue when he do a return he need to have a differnt count numbers
than the transactions number for example when he sale something the
transation number 1545 is generated but is he return or do a store credit
the transaction number is 145 without affectin the secuence os the sale
transaction number. can be done?
the other probl...Extracting Pivotable Selections
In a field, if you elect to only include certain items,
then when you select all of those items the table displays
(Multiple Items) rather than (All). Is there a way to
obtain a list of the items that are included (or excluded)
from the Multiple Items group? I know how to see the
list, but is there a way to move that info to cells in the
The following code will print the page field items that aren't hidden:
'lists visible page field items on a new worksheet
Dim ws As Worksheet
Dim pt As PivotTable
Dim pf A...Macro for detect palindromes and repeats in letters/numbers string
I am looking to detect palindromes (sentence or number or other
sequence of units that can be read the same way in either direction)
and repeats (sequences of letters or numbers which are repeating
atleast twice within a string) in some strings containing between
20-5000 letters. Has somebody any idea how could I perform that using
an Excel macro? I would like that the string to be evaluated could be
on cell "A1" and that the detected palindromes and repeats could be
listed bellow A2 and C2, respectively; and that the number of times
that they appear in the sentence could b...The number 1 is appened to filename after file is opened
I am trying to find out why the number 1 is added to the file name after it
is opened in Excel? I am using Excel from Office 97. It only happens when I
open the file from Windows Explorer or from a shortcut on my desktop.
It sounds like windows is using New as the default action when you doubleclick
on the filename in windows explorer.
If you right click on a *.xls file, you'll see a bunch of options--one will be
Open and one will be New. Open should be the bold (default) one.
I bet you'll see New in bold.
If that's the case, you can try this:
Close Excel and
Windows Start...Re: Selective Averaging
Sorry I inadvertently deleted the duplicate A. It should read
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Bob Phillips" <email@example.com> wrote in message news:...
> Hi Frank,
> This should do it
> Bob Phillips
> ... looking out across Poole Harbour to the Purbecks
> (remove nothere from the email address if mail...SendObject acSendNoObject Selecting Emails and Outlook
What i have is a list box that displays two fields from my table, it
displays a name of a charity and their email address. This list box allows
me to select multiple values. I have a command button with no function. What
i want to happen is when i click on the button, it will take the email
addresses of the charitys selected and populate the To: field in Microsoft
Outlook. People have been referring me to websites and i am just not gettin
the idea of things. Can someone please post the code for this?
Table and form name = Charities
List box name = List1
Field Name in list box = Name...Lookups and References in relations to countA and sum products....
I have 2 sheets, one summary, and one detail.
The detail is as follows:
The summary is as follows:
Dept # of styles
I want the formula on the summary sheet to count the number of non
blank entries for each dept.
Which formula is it?
JR573PUTT's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31587
View this thread: http://ww...