deleting multiple rows
I need to be able to delete multiple rows out of a worksheet. What I need to
be able to do is have a function/macro that can identify a string of
characters and then delete all the rows that have those characters.
For example, if row 4, 18, and 45 all had "blue" in them (assume column is
A), then I need something to go delete row, 4, 18, and 45.
At the top of my worksheet I will have several, probably about 3, criteria,
such as, "blue", "red", and "green" that will all need to be deleted.
Now here's where I really need help. A row may ...Range Names
I need to figure out how to automatically assign dynamic range names to
each "Saturday to Friday" in a list of monthly day numbers and associated
day names for an entire year.
I don't know where to start.
Depending on what you wish to do, XL's built in WeekNum function may assist.
"Wes" <firstname.lastname@example.org> wrote in message
>I need to figure out how to automatically assign dynamic range name...only the first 5 columns of a 10 column excel spreadsheet sort
How do I get the whole spread sheet to sort? There is a blue lox for the
first 5 columns that limits the range of the sort. How do I remove it?
Using Office 2003.
Maybe if you remove the Data|list
Select a cell in that blue box.
Data|list|convert to range
> How do I get the whole spread sheet to sort? There is a blue lox for the
> first 5 columns that limits the range of the sort. How do I remove it?
> Using Office 2003.
...Hide columns according to background fill color
I am having trouble understanding how Excel handles colors.
I have a public sub that sets a public variable, "TermColor" using the RGB function. TermColor is of type MsoRGBType.
In another module, I use the TermColor variable as follows:
Sub WeedColsByColor(ByRef Clr, ByRef WS)
Dim LastCol, i As Long
LastCol = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count
'hide columns if they have one of the forbidden colors
Debug.Print (CBool(.Cells(2, i).Interior.Color = Clr))
Can anyone tell me why, when I select a cell near the bottom of a page, 3-4
rows seem to be sected as well?
Thanks in advance
You are probably in Extend mode. Look at the bottom right of the Status
Bar, look for Extend, its location and wording depend on version. If it is
on press Esc or F8.
If this helps, please click the Yes button
> Can anyone tell me why, when I select a cell near the bottom of a page, 3-4
> rows seem to be sected as well?
> Thanks in advance
I am having this p...Select a certain number of cells in a row
I'd like to have the macro to select row 5 to 10 in the active column. May I
know what is the VB code to write?
dim myRng as range
dim myCol as long
mycol = activecell.Column
set myrng = .range(.cells(5,mycol),.cells(10,mycol))
I'm not quite sure why you want to select that range. But for the most part, if
you act directly on the range (and avoid .selects), you're code will work faster
and be easier to modify.
Dolph...Can there be variable size columns in one report?
I want to create a report that has 3 sub-reports of different column widths.
Is this possible?
-The 1st sub-report has 1 column that occupies the entire width of the page
-The 2nd sub-report can fit 2 columns in the page width
-The 3rd sub-report can fit 3 columns in the page width
Subreports can have any number of columns that don't have to be the same from
one to another. Typically your columns should display across then down in
order to render properly as a subreport.
Microsoft Access MVP
> I want to create a r...Excel 2003: How to make transparent columns in Excel chart?
If you create a bar plot froma given dataset you can format the
columns by right clicking and choosing the desired options. In the tab
that opens there is a slider which is supposed tho set the level of
transparency of the column (selceted area). But so far i couldn't find
a way how to use this slider.
I know that there is an alternative way to get transparent bars by
creating a rectangular object formating it and the use copy -> paste.
But i wonder what is the slider for if you can't use it?
Does anybody know have an idea?
...Combining Pivot Tables
I have a data set of around 100,000 rows which I have imported into
excel in two sheets (~50,000 rows each).
The data is not in a format that excel can easily parse into a pivot
table directly from the source - it requires some formulae in excel to
be able to use a pivot table.
I have used a pivot table on each of those sheets to summarise the
data, and that works fine.
However, I would like to be able to get a single summary pivot table
from the two sheets (or from the two pivot tables).
Is that possible, and if so, how do I go about it?
"Alan" <...Combine rows to print on one label
I have imported student information into an excel spreadsheet (use Excel
2003). Each student has one row of information for each course they take. I
need to print one label for each student and each course & grade must print
on a single label. How do I combine the rows to cause this to happen?
assuming the info in the row is divided into separate columns for name,
course, grade, etc you can do a mail merge in word using the spreadsheet as
the data source
> I have imported student information into an excel spreadsheet (use Excel
> 2...Column width
In Sheet 1 I have a certain amount of data, I want to select some cells and
copy them to Sheet 2 keeping the same format. When I do this, the fonts and
the colours remain unchanged, but the column width don't. I have tried paste
special, but couldn't figure it out. Is this possible?
Thanks in advance
--Select the target cell and right click >PasteSpecial>All>OK
--Keeping the target selection right click>PasteSpecial>select ColumnWidth>OK
If this post helps click Yes
"Emece"...building a range for SetSourceData
I am trying to build a range object to feed a chart input. I want to
move from old to new:
myChart.SetSourceData Source:=Sheets("Main Sheet").Range( _
myChart.SetSourceData Source:=ws , PlotBy:=xlRows
Here is the way I built the robj. I use the other range objects to
collect row and column info. What am I doing wrong?
Dim ws As Range
Set ws = myWs.Range( _
myWs.Cells(Date_Week.row, LastDataC...How do i keep the colors applied to the specific rows when sortin.
I have applied specific colors to specifc rows, but when i sort the colored
rows follow the sorting format.
...deleting duplicate rows
I have an import file that might have duplicates that I don't want to deal
with. I'm creating a temp table from the import file and want to delete the
duplicates from the temp table before proceding with other code.
Here's an example table:
create table #temp (itemnum int, firstname varchar(10), notes varchar(50))
insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some
insert into #temp (itemnum, firstname, notes) values (2, ...Name range within a name range
In Excel is it possible to create a name range that contains multiple name
ranges. As an example, I have created several name ranges with one cell
reference, such as Sales(A2), COS(A5), ADM(A7), SELLING(A8), MRKT(A9). I
would like to create a name range that I could include the all the name
ranges and call it GROSS_PROFIT. If later I add another range I would like
to be able to just add the range name to GROSS_PROFIT, such as R&D.
Also the reason I chose to use named ranges is my cell references can change
from month to month, I find it easier to create formulas using name
refer...Reverse sign of range
I have a routine that reverses the sign of a list of data. This particularly
useful when entering a list of items and discovering that they're the wrong
However, it relies on cell A1 being available to create the -1 in the
The drawback with this is that it doesn't work on protected sheets (unless
A1 is unprotected, of course).
Is there a better way of putting -1 into the clipboard ?
' Check that a file is open before attempting to run the procedure
If IsFileOpen() = False Then Exit Sub
' cFormula is the formula c...Add values in a column according to value in another column
How can I add the values in a column according to values in another
column? If there is any value in a row in column B, I want to include
the value of the corresponding row in column A. I'm flexible as to
whether this is ANY value (i.e. not empty) or greater than zero.
Maybe something like this =IF(B1="","",IF(B1>0,B1+A1))
"Paul Kaye" <email@example.com> wrote in message
> How can I add the values in a column according to values in another
> colu...How do I UN Center a Selection
I have a heading, Columns B thru K, text in B and centered across selections.
No matter what I try I can't really get rid of the range selection.
If I undo the format, alignment. OK
Then go and retry it except to B thru F, it reverts back to the B thru K ???
Excel 97 and XP ?
This time it's really weird, I deleted the entire row, and went back in.
It still remembers the range ??
I think xl2k and xl2002 have gotten better with this.
But I don't have xl97 anymore, so these are guesses.
Select B1:K1 (or whatever row)
if that f...trying to select the last 3 digits of a field
this is the query im using (I am looking to get the last 3 characters
of a field):
And I get this error:
[Informix][Infomirx ODBC Driver][Informix]A syntax error has occurred.
Is it something wrong with my query or something outside Access 2003
(since the table I am trying to work with its using an ODBC conexion)?
...How do I make a column be my default column in Access
I need to make my desricption field my default field. How do I do that? Right
not it defaults to my items field.
or in macro
>I need to make my desricption field my default field. How do I do that? Right
>not it defaults to my items field.
Message posted via AccessMonster.com
Or, if you don't want to use events, simply set your tab order from the form
R...Resize rows in a protected sheet
Hi there. I'm sending out a protected worksheet with some
cells that the users can enter variable amounts of text.
I've set the size so they can comfortably fit in about 50
words but I'd like them to have the flexibility to resize
the row height when they're editing the worksheet. But
because it's protected, they can't.
Is there anyway I can allow them to change the row height
dynamically? A button or something??
A workaround. Format the alignment in the cell to Wrap
text. Users can Keep entering text or returns (ALT+ENTER)
in the cell and it will aut...How to draw a selection line? #2
I want to draw selection lines with 1 pixel width dotted line style.
normally it works ok, but the it looks not very good when I zoom the image
to a bigger magnification. What I want is like the effect in many popular
image processing softwares such as Adobe Photoshop. for example, I make a
ellipic selection and draw a dotted line ellipse it looks ok. but when
zooming in it still the same as normal ,but in photoshop it became aliasing
lines which seems to be composed by several small blocks. Does any have any
idea on this question??
any help would be appreciated.
...Copying non-adjacent columns to adjacent rows
Hi all, I consider myself fluent in Excel, but I've developed a situation
that has stumped me. Any help would be much appreciated.
I might be able to solve this issue if somebody could show me how to add a
number to a column. For example, if I want Excel to pull data from Column
D, how can I get Excel to realize that column D is really the same thing as
Column A + 3? I know you can use the column() command to get the numerical
value for a column, but is there a way to have it do that in reverse, such
that you could tell it the column number is 4 and it would know that you are
I am having an issue with autofitting rows in a spreadsheet of imported text
in Excel 2003. There are no merged cells in the document, and none of the
cells go over the 1024 character limit. When I select all rows and attempt to
autofit the rows to the contents of the cells, some of the rows resize
properly, and some leave one or several lines of white space below the bottom
of the text in the longest cell. I can resize them manually, but this is data
that is imported daily and runs to several thousand rows. If I widen the
column where I am having the problem, once it reaches a certain ...Deleting multiple rows through a formula
So does anyone know a formula that will allow me to delete multiple rows of
re-occuring data in a spreadsheet.
For example, I have a spreadsheet of 10,000 lines, with multiple mobile
numbers on it. I need to only have one of each mobile number and delete the
remaining rows where the mobile has been repeated?
you may be able to use the advance filter where can select unique records
select data filter advanced filter select copy to another location then in
list range box enter range to filter in the copy to box enter a free column
select unique records only then ok
then just ...