Delete row with empty cell in Column A?
I have a .CSV file exported from our parts control application that needs a
bit of tweeking to make it presentable.
I would like to delete any row that has an empty cell in Column A. How can
I do this? Does it require a macro?
Doesn't require a macro. Select column A. Choose Edit/Go To.../Special.
Select Blanks. Choose Edit/Delete... and choose Entire Row from the
dialog that opens.
In article <epn13akPGHA.648@TK2MSFTNGP14.phx.gbl>,
"SteveK" <firstname.lastname@example.org> wrote:
> I have a .CSV file exported from our parts control ap...How to take a list of cells and sort them into a new list
Let's say I have a list of 20 student numbers, unsorted, in cells A3:A22.
How, if possible, can I set up my worksheet so that it will automatically
take the values in that range of cells, sort them, and display them in cells
C3:C22 in sorted order?
Thanks for any help.
> Let's say I have a list of 20 student numbers, unsorted, in cells A3:A22.
> How, if possible, can I set up my worksheet so that it will automatically
> take the values in that range of cells, sort them, and display them in
> C3:C22 in sorted order?
One way to set it...Selecting A Range Of Cells Depending on Start And End Date
Im trying to write a function that will allow me to work out a sum o
cells in a particular column depending on a start cell, and finishin
on an end cell... But, i want the start and end to correspond wit
dates, and have input boxes pop up asking for the required range o
I.e. A1:A365 = 1st Jan:31st Dec, So from entering 2nd Feb as the start
and 1st March, and column c. id like to sum the contents of those cell
in column c.
Message posted from http://www.ExcelForum.com
...How do I find the cell address of the 2nd largest of a set?
I have a table of integers ranging from cells B3 to Z51, and I want to find
the cell address of the second-largest and third-largest value.
=LARGE(B3:Z51, 2) will give me the second-largest value, but how do I find
the cell address where that value is located?
Also, when there is the exact same value in two different cells, I want to
be able to find both cell addresses. For example, LARGE(B3:Z51, 3) will find
the third-largest value, and LARGE(B3:Z51, 4) will find the fourth-largest
value. If those two values are the same, how do I find their corresponding
See, what I'm...How to quit the application if user select "Disable macro"?
I tried to force run macro, but it said there is no way to do it, so my
second question is how to terminate the application if the macro is
I have an excel file in which cells are need to proected by disable the
selection. Is there a way to disable selection from UI? I know I can lock
and hide it.
Thanks in advance!!
Here is a general approach that I have used:
Create a workbook that only shows one sheet with the instructions: "Close
this workbook and reopen it with Macros enabled". Use code to hide this
sheet and "reveal" the rest of the workbook when the boo...Auto Fill Cells
I am building an inpatient log for a nursing home. I need to auto fill the
patient's name and address from their ID #
Example in column C (ID#) 255 - would automatically fill in Column D (last
name), Column E(first name), Column F(middle initial), and Column G(address)
At this point - all of the cells are blank.
Sounds nice. Where is the data coming from? Take a look at these links for
some ideas of how you can go about doing this:
http://www.contextures.com/xlFunc...Find value in a Range of Cells and perform Conditional Formatting
I want to apply a Conditional Formula to Game Board (A2 to E6). If these
numbers are found in the range of Result Numbers (F2 to AD2), then the Game
Board numbers background color should change to white.
Sample Excel Sheet Screenshot available here:
Select A2 then drag to select your game board (A2:E6) then apply a
conditional format of
Select a colour OK
With F2 AD2 empty all the cells will now be you chosen colour and will turn
white again whn a number in the board matches A2 AD2
W...Error checking a cell?
I want to do error checking on a data entry cell and can't figure out
how to do it without an illegal circular reference. I don't want to
have hidden cells. Let's say that I am entering data in a cell that is
always between 1 and 10. I want to be sure that if I introduce a typo
with the decimal point like 83 that the cell corrects this to be 8.3 .
How can I do that? Any IF statement on a cell itself results in a
For example, I can't come up with a statement for a cell, say B23,
that says something like
Is it possible? Than...How do I select the email sending account for a new message
Operating System: Mac OS X 10.6 (Snow Leopard)
Email Client: pop
HI, im using Entourage with two separate POP email accounts. How do I select the account I want to use for sending new email messages?
On 4/9/10 9:59 PM, Fbazo@officeformac.com wrote:
> pop HI, im using Entourage with two separate POP email accounts. How do
> I select the account I want to use for sending new email messages?
When you create a new message, it will default to the default account
you have set in Entourage preferences. Set in Reply & Forward.
If you need to s...How could I select Multiple options in options group in form
I would like to create a Option Group in my form and let user to choose
multiple options to print the deferent report.
You can't choose multiple options from an option group. You could use
whatever control you are using for the options (radio buttons, check boxes,
etc) individually. Then you would have to make your selections for your
report based on which controls are selected.
Dave Hargis, Microsoft Access MVP
> I would like to create a Option Group in my form and let user to choose
> multiple options to print the deferent report.
I am a VBA novice and posted some code like:
ActiveCell.FormulaR1C1 = "=IF(R2C2>0,""Yes"",""No"")"
ActiveCell.Value = 1
The groans were almost audible over the Internet! While this code gets the
job done, I get the distinct impression that this is the slowest and
clunkiest way to do things. How else do I indicate the sheets and cell I
want to work in without selecting it?
.Range(&qu...How to round cells to two significant figures
I've been doing masses of calculations on excel and now i want to round my numbers to two significant figures quickly without having to do them one at a tim
Direct from the Help file..........
Rounds a number to a specified number of digits.
Number is the number you want to round.
Num_digits specifies the number of digits to which you want to round
If num_digits is greater than 0 (zero), then number is rounded to the
specified number of decimal places.
If num_digits is 0, then number is rounded to the neares...Open a form based on combobox selection
I have a main form that contains a combobox with several options, I would
like the user to be able to select an option from this combo box and have
another form open automatically based on the user selection. For example
if the the user selects "cars" from combobox then a "CarDataEntry" from
would open, or if the user selects "planes" then the "PlanesDataEntry" form
What would be the best way to accomplish this?
I appreciate your help
Assuming your combo box row source is a value list, you could make it a two
column combo with th...Help! Inserting numbers in excel cells
I am typing up tickets for a raffle and there are already over a
thousand cells. I now want to insert numbers to them so that when they
print, each ticket has a number- 1 thru 1,500.
Anyone know how to do this?
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/
Are you using Excel? Are you printing 1 ticket per sheet? You can stick this
Range("A1").Value = Range("A1") + 1
Greeting from the Gulf Coa...SELECT FROM Query name
I am using a SELECT statement as a rowsource on a form listbox. I have a
query named 01-Accounts Query and the SELECT stmt reads as follows...
SELECT 01-Accounts_Query.Name, 01-Accounts_Query.Date FROM 01-Accounts_Query
I get a syntax error in query message. How do I fix this?
>I am using a SELECT statement as a rowsource on a form listbox. I have a
>query named 01-Accounts Query and the SELECT stmt reads as follows...
>SELECT 01-Accounts_Query.Name, 01-Accounts_Query.Date FROM 01-Accounts_Query
>I get a syntax error in query message...Import selected worksheet from another workbook
I'm stuck on the best way to prompt the user to open another workbook and
select a worksheet to be copied into the file containing the macro. Ideally,
I would hardcode the name of the target worksheet, leaving the user to just
locate the right file.
However, I need to include something that will also allow the user to select
the specific worksheet to copy, just in case there's been some unauthorised
Is this possible?
Many thanks for your help
How about a compromise?
Tell the user to open the "sending" workbook first, then you can ask them...Does variable set to cell value automatically becomre cell value?
I think I know the answer to this, but I want to be sure. I'm incrementing
a value that is in a cell location describe in part by a variable. The
formula looks like:
ws.Cells(iMoRow, "T").Value = _
ws.Cells(iMoRow, "T").Value + iOVComplCON
So the value in the cell is incremented by the variable "iOVComplCON"
amount. No problem.
Is this the same thing?
iWt = Cells(iMoRow, "T").Value
iWT = Cells(iMoRow, "T").Value + iOVComplCON
or do I have to have another line added to the two above:
Cells(iMoRow, "T").Valu...Can I seperate Text and Numbers from one cell, and drop into two cells?
I have a row of 1,400 entries, each is in the form:
"Some number" - "Some Text description"
450938-Magic Marker Blue
I want to pull the numbers out of the cell and drop into their ow
cell, but without tediously going through each cell and retyping,
cannot think of an alternate method.
The only pattern here is that each set of numbers is between 5 and 1
characters, and that the text description is separated by a hyphe
Any ideas out there?
Message posted from http://www.ExcelForum.com
Hi shade...setting a cells value using range
what am i doing wrong
dim myRange as Range
dim iNum as integer
Set myRange = Cells(iNum + 2, 1)
myRange.Value = 34
trying to set a cell myRange to cell(8,1)
then putting the value 34 into the cell
Works fine for me
You don't try to do this in a Function, do you?
It should work in a Sub
"cantonarv" <email@example.com> wrote in message
> what am i doing wrong
> dim myRange as Range
> dim iNum as integer
> iNum= 6
> Set myRange = Cells(iNum + 2,...Set certain cells to print and others not to print
I am working on a form in XL2007.
The form consumes one 8.5 by 11 page.
I would like to set certain cells to be visible on screen, but not print
their contents. This would need to be done almost on a cell by cell basis. Is
something like this possible?
There is a special cell format for this.
Search excel's help for "Hide or display cell values"
"xp" <firstname.lastname@example.org> wrote in message
> I am working on a form in XL2007.
> The form consumes one 8.5 ...Conditional Cell Protection
I need to be able to prevent any other information being entered in a
column once one cell has been entered.
For example if someone inputs information into cell A1, I do not want
anyone else to be able to enter anything anywhere else in Column A or
for there to be a warning before they do so.
Is this possible?
Private Sub Worksheet_Change(ByVal Target As Range)
Dim ans As Long
On Error GoTo ws_exit:
Application.EnableEvents = False
If Application.CountA(.EntireColumn) > 0 Then
ans = MsgBox("Data already present" & vbNewLine &...Cell Protection #10
I have a worksheet with five columns that contain IF formulas (Group A)
linked back to other columns in the same worksheet (Group B). I need to
protect the formulas in those five columns (Group A), so that they cannot be
deleted/changed. I have followed the correct procedures to lock the specific
cells and password-protect the worksheet.
One of the columns in “unlocked” Group B contains links to two other files.
The users need the ability to change the links to other file sources. When
the columns in Group A are locked and protected, the user cannot change the
Is there...FILES made of cells
How do I make a text file which every line in it is composed of several
line 1> C1 +' '+ D1 + ' ' + F1 etc
line2 > C2 .....F2
How do I generate that var that will be writen to the text file line by line
Insert a new worksheet in the file, and in A1 of this worksheet enter
=3DSheet1!C1&" "&Sheet1!D1&" "&Sheet1!E1&" "&Sheet1!F1
Copy this down as required.
When doing File | Save As with this new sheet selected, change the
File type to Text (*.txt), and it will only save the...Inserting a space between characters in a cell
I have a column of codes and some are written as below:
I want them all to has a space after the A2 ie. A2 ABC, A2 BBB etc etc.
What is the best/easiest way to do this?
use a helper column off to the side. use this formula
adjust cell reference to fit your data.
copy down as far as needed.
copy the helper column and paste special values.
you can then replace the old data with the new.
> I have a column of codes and some ...Locking in a Snag-it(jpg) into a Cell so it stays with that row
So I have created a snap shot using snag-it off my computer. I have
pasted it into my Issues Log, in the appropriate row. I want this snap
shot to stay with its row when I filter, etc. Is this possible. Can I
lock a snap shot (jpg) into a cell? Please let me know how.
I've always had good luck if I make sure the picture is within the cell's (or
row's) borders. If I'm overlapping an edge, then it doesn't work the way I want.
And I'd verify that I didn't change anything in the properties, too.
Rightclick on the picture
Choose Format Picture