Selecting a Range of Columns for Variable Rows

I want to do something like this:
Select a sheet
for row=3 to 17
select cells A:F
copy the cells
Select A2 on another sheet
Paste what I copied.
Shift down 1 row
next row.

I have verything down pretty well except selecting A:F for each row as it 
comes up.
I would appreciate any help you can give me.
Thanks.
-- 
BillR
0
Utf
4/14/2010 4:44:01 PM
excel.programming 6508 articles. 2 followers. Follow

6 Replies
1102 Views

Similar Articles

[PageSpeed] 51

It would be helpful if you post the code you have so far.  It helps to fill 
in the gaps in your narrative.


"BillR" <BillR@discussions.microsoft.com> wrote in message 
news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
>I want to do something like this:
> Select a sheet
> for row=3 to 17
> select cells A:F
> copy the cells
> Select A2 on another sheet
> Paste what I copied.
> Shift down 1 row
> next row.
>
> I have verything down pretty well except selecting A:F for each row as it
> comes up.
> I would appreciate any help you can give me.
> Thanks.
> -- 
> BillR 


0
JLGWhiz
4/14/2010 5:03:05 PM
Hi

Here's a way:

Dim DestCell As Range
Set DestCell = Worksheets("Sheet2").Range("A2")

For rw = 3 To 17
    With Worksheets("Sheet1")
        .Range("A" & rw & ":F" & rw).Copy Destination:=DestCell
    End With
    Set DestCell = DestCell.Offset(1, 0)
Next

Or  it can be done with this one-line statement:

Worksheets("Sheet1").Range("A3:F17").Copy Worksheets("Sheet2").Range("A2")

Regards,
Per


"BillR" <BillR@discussions.microsoft.com> skrev i meddelelsen 
news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
> I want to do something like this:
> Select a sheet
> for row=3 to 17
> select cells A:F
> copy the cells
> Select A2 on another sheet
> Paste what I copied.
> Shift down 1 row
> next row.
>
> I have verything down pretty well except selecting A:F for each row as it
> comes up.
> I would appreciate any help you can give me.
> Thanks.
> -- 
> BillR 

0
Per
4/14/2010 5:08:16 PM
Here is the code. It works, but I have been unable to select more than one 
column at a time. This causes much flicker on the screen and looks like I 
have absolutely no idea what I am doing. I think that may be right.

Sheets("CARBWORKSHEET").Select
counter = 3
    For counter = 3 To 17
' Sheets("CARBWORKSHEET").Select
If Worksheets("CARBWORKSHEET").Cells(counter, 4).Value > 0 Then
    For col = 1 To 6
        Sheets("CARBWORKSHEET").Select
        Set curCell = Worksheets("CARBWORKSHEET").Cells(counter, col)
        curCell.Select
        Selection.Copy
        Sheets("DailyRecord").Select
        Set newcell = Worksheets("DailyRecord").Cells(2, col)
        newcell.Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, 
Operation:=xlNone, SkipBlanks:=True, Transpose:=False
        Application.CutCopyMode = False
        With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        End With
        With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
        End With
    Next col
Else
    col = 1
End If
Set skipcell = Worksheets("DailyRecord").Cells(2, 4)
If skipcell.Value > 0 Then
    Sheets("DailyRecord").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Selection.ClearFormats
    End If
Next counter

-- 
BillR


"JLGWhiz" wrote:

> It would be helpful if you post the code you have so far.  It helps to fill 
> in the gaps in your narrative.
> 
> 
> "BillR" <BillR@discussions.microsoft.com> wrote in message 
> news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
> >I want to do something like this:
> > Select a sheet
> > for row=3 to 17
> > select cells A:F
> > copy the cells
> > Select A2 on another sheet
> > Paste what I copied.
> > Shift down 1 row
> > next row.
> >
> > I have verything down pretty well except selecting A:F for each row as it
> > comes up.
> > I would appreciate any help you can give me.
> > Thanks.
> > -- 
> > BillR 
> 
> 
> .
> 
0
Utf
4/14/2010 7:43:01 PM
I believe this will cover everything.  If not, post back.
Your code indicated that Column D was tested for a value
greater than zero and if found, copy to a second sheet with
the objective being to copy columns A thru F of that row to
the second sheet and to remove any cell coloring if it existed.
The last part of your code appeared to test for any entries that
might have been pasted to row two of the second sheet and if found
insert a row to move the data downward.  This code attempts to
cover all of those items.  Good luck.


  Dim sh1 As Worksheet, sh2 As Worksheet
   Dim rng As Range, cRng As Range, lr As Long

    Set sh1 = Sheets("CARBWORKSHEET")
    Set sh2 = Sheets("DailyRecord")
    Set rng = sh1.Range("D3:D17")

    For Each c In rng
       If c.Value > 0 Then
          Set cRng = sh1.Range("A" &c.Row & ":F" & c.Row)
          lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
          If lr < 2 Then lr = 2
          cRng.Copy sh2.Range("A" & lr + 1)
          sh2.Range("A" & lr + 1).EntireRow.Interior.Pattern = xlNone
        End If
     Next

P.S.  The flicker and flash should also be gone.




"BillR" <BillR@discussions.microsoft.com> wrote in message 
news:6AA70DF4-8363-4C2A-8962-BEF164F8E3B3@microsoft.com...
> Here is the code. It works, but I have been unable to select more than one
> column at a time. This causes much flicker on the screen and looks like I
> have absolutely no idea what I am doing. I think that may be right.
>
> Sheets("CARBWORKSHEET").Select
> counter = 3
>    For counter = 3 To 17
> ' Sheets("CARBWORKSHEET").Select
> If Worksheets("CARBWORKSHEET").Cells(counter, 4).Value > 0 Then
>    For col = 1 To 6
>        Sheets("CARBWORKSHEET").Select
>        Set curCell = Worksheets("CARBWORKSHEET").Cells(counter, col)
>        curCell.Select
>        Selection.Copy
>        Sheets("DailyRecord").Select
>        Set newcell = Worksheets("DailyRecord").Cells(2, col)
>        newcell.Select
>        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
> Operation:=xlNone, SkipBlanks:=True, Transpose:=False
>        Application.CutCopyMode = False
>        With Selection
>        .HorizontalAlignment = xlLeft
>        .VerticalAlignment = xlBottom
>        End With
>        With Selection.Interior
>        .Pattern = xlNone
>        .TintAndShade = 0
>        .PatternTintAndShade = 0
>        End With
>    Next col
> Else
>    col = 1
> End If
> Set skipcell = Worksheets("DailyRecord").Cells(2, 4)
> If skipcell.Value > 0 Then
>    Sheets("DailyRecord").Select
>    Rows("2:2").Select
>    Selection.Insert Shift:=xlDown
>    Selection.ClearFormats
>    End If
> Next counter
>
> -- 
> BillR
>
>
> "JLGWhiz" wrote:
>
>> It would be helpful if you post the code you have so far.  It helps to 
>> fill
>> in the gaps in your narrative.
>>
>>
>> "BillR" <BillR@discussions.microsoft.com> wrote in message
>> news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
>> >I want to do something like this:
>> > Select a sheet
>> > for row=3 to 17
>> > select cells A:F
>> > copy the cells
>> > Select A2 on another sheet
>> > Paste what I copied.
>> > Shift down 1 row
>> > next row.
>> >
>> > I have verything down pretty well except selecting A:F for each row as 
>> > it
>> > comes up.
>> > I would appreciate any help you can give me.
>> > Thanks.
>> > -- 
>> > BillR
>>
>>
>> .
>> 


0
JLGWhiz
4/14/2010 10:19:33 PM
It not only solved the problem, it eliminated nearly a page of code. Thanks 
for the help and rapid response.
-- 
BillR


"JLGWhiz" wrote:

> I believe this will cover everything.  If not, post back.
> Your code indicated that Column D was tested for a value
> greater than zero and if found, copy to a second sheet with
> the objective being to copy columns A thru F of that row to
> the second sheet and to remove any cell coloring if it existed.
> The last part of your code appeared to test for any entries that
> might have been pasted to row two of the second sheet and if found
> insert a row to move the data downward.  This code attempts to
> cover all of those items.  Good luck.
> 
> 
>   Dim sh1 As Worksheet, sh2 As Worksheet
>    Dim rng As Range, cRng As Range, lr As Long
> 
>     Set sh1 = Sheets("CARBWORKSHEET")
>     Set sh2 = Sheets("DailyRecord")
>     Set rng = sh1.Range("D3:D17")
> 
>     For Each c In rng
>        If c.Value > 0 Then
>           Set cRng = sh1.Range("A" &c.Row & ":F" & c.Row)
>           lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
>           If lr < 2 Then lr = 2
>           cRng.Copy sh2.Range("A" & lr + 1)
>           sh2.Range("A" & lr + 1).EntireRow.Interior.Pattern = xlNone
>         End If
>      Next
> 
> P.S.  The flicker and flash should also be gone.
> 
> 
> 
> 
> "BillR" <BillR@discussions.microsoft.com> wrote in message 
> news:6AA70DF4-8363-4C2A-8962-BEF164F8E3B3@microsoft.com...
> > Here is the code. It works, but I have been unable to select more than one
> > column at a time. This causes much flicker on the screen and looks like I
> > have absolutely no idea what I am doing. I think that may be right.
> >
> > Sheets("CARBWORKSHEET").Select
> > counter = 3
> >    For counter = 3 To 17
> > ' Sheets("CARBWORKSHEET").Select
> > If Worksheets("CARBWORKSHEET").Cells(counter, 4).Value > 0 Then
> >    For col = 1 To 6
> >        Sheets("CARBWORKSHEET").Select
> >        Set curCell = Worksheets("CARBWORKSHEET").Cells(counter, col)
> >        curCell.Select
> >        Selection.Copy
> >        Sheets("DailyRecord").Select
> >        Set newcell = Worksheets("DailyRecord").Cells(2, col)
> >        newcell.Select
> >        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
> > Operation:=xlNone, SkipBlanks:=True, Transpose:=False
> >        Application.CutCopyMode = False
> >        With Selection
> >        .HorizontalAlignment = xlLeft
> >        .VerticalAlignment = xlBottom
> >        End With
> >        With Selection.Interior
> >        .Pattern = xlNone
> >        .TintAndShade = 0
> >        .PatternTintAndShade = 0
> >        End With
> >    Next col
> > Else
> >    col = 1
> > End If
> > Set skipcell = Worksheets("DailyRecord").Cells(2, 4)
> > If skipcell.Value > 0 Then
> >    Sheets("DailyRecord").Select
> >    Rows("2:2").Select
> >    Selection.Insert Shift:=xlDown
> >    Selection.ClearFormats
> >    End If
> > Next counter
> >
> > -- 
> > BillR
> >
> >
> > "JLGWhiz" wrote:
> >
> >> It would be helpful if you post the code you have so far.  It helps to 
> >> fill
> >> in the gaps in your narrative.
> >>
> >>
> >> "BillR" <BillR@discussions.microsoft.com> wrote in message
> >> news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
> >> >I want to do something like this:
> >> > Select a sheet
> >> > for row=3 to 17
> >> > select cells A:F
> >> > copy the cells
> >> > Select A2 on another sheet
> >> > Paste what I copied.
> >> > Shift down 1 row
> >> > next row.
> >> >
> >> > I have verything down pretty well except selecting A:F for each row as 
> >> > it
> >> > comes up.
> >> > I would appreciate any help you can give me.
> >> > Thanks.
> >> > -- 
> >> > BillR
> >>
> >>
> >> .
> >> 
> 
> 
> .
> 
0
Utf
4/15/2010 12:38:01 PM
Glad I could help.


"BillR" <BillR@discussions.microsoft.com> wrote in message 
news:F1191432-32C6-4754-A6D8-D19A31B87111@microsoft.com...
> It not only solved the problem, it eliminated nearly a page of code. 
> Thanks
> for the help and rapid response.
> -- 
> BillR
>
>
> "JLGWhiz" wrote:
>
>> I believe this will cover everything.  If not, post back.
>> Your code indicated that Column D was tested for a value
>> greater than zero and if found, copy to a second sheet with
>> the objective being to copy columns A thru F of that row to
>> the second sheet and to remove any cell coloring if it existed.
>> The last part of your code appeared to test for any entries that
>> might have been pasted to row two of the second sheet and if found
>> insert a row to move the data downward.  This code attempts to
>> cover all of those items.  Good luck.
>>
>>
>>   Dim sh1 As Worksheet, sh2 As Worksheet
>>    Dim rng As Range, cRng As Range, lr As Long
>>
>>     Set sh1 = Sheets("CARBWORKSHEET")
>>     Set sh2 = Sheets("DailyRecord")
>>     Set rng = sh1.Range("D3:D17")
>>
>>     For Each c In rng
>>        If c.Value > 0 Then
>>           Set cRng = sh1.Range("A" &c.Row & ":F" & c.Row)
>>           lr = sh2.Cells(Rows.Count, 1).End(xlUp).Row
>>           If lr < 2 Then lr = 2
>>           cRng.Copy sh2.Range("A" & lr + 1)
>>           sh2.Range("A" & lr + 1).EntireRow.Interior.Pattern = xlNone
>>         End If
>>      Next
>>
>> P.S.  The flicker and flash should also be gone.
>>
>>
>>
>>
>> "BillR" <BillR@discussions.microsoft.com> wrote in message
>> news:6AA70DF4-8363-4C2A-8962-BEF164F8E3B3@microsoft.com...
>> > Here is the code. It works, but I have been unable to select more than 
>> > one
>> > column at a time. This causes much flicker on the screen and looks like 
>> > I
>> > have absolutely no idea what I am doing. I think that may be right.
>> >
>> > Sheets("CARBWORKSHEET").Select
>> > counter = 3
>> >    For counter = 3 To 17
>> > ' Sheets("CARBWORKSHEET").Select
>> > If Worksheets("CARBWORKSHEET").Cells(counter, 4).Value > 0 Then
>> >    For col = 1 To 6
>> >        Sheets("CARBWORKSHEET").Select
>> >        Set curCell = Worksheets("CARBWORKSHEET").Cells(counter, col)
>> >        curCell.Select
>> >        Selection.Copy
>> >        Sheets("DailyRecord").Select
>> >        Set newcell = Worksheets("DailyRecord").Cells(2, col)
>> >        newcell.Select
>> >        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
>> > Operation:=xlNone, SkipBlanks:=True, Transpose:=False
>> >        Application.CutCopyMode = False
>> >        With Selection
>> >        .HorizontalAlignment = xlLeft
>> >        .VerticalAlignment = xlBottom
>> >        End With
>> >        With Selection.Interior
>> >        .Pattern = xlNone
>> >        .TintAndShade = 0
>> >        .PatternTintAndShade = 0
>> >        End With
>> >    Next col
>> > Else
>> >    col = 1
>> > End If
>> > Set skipcell = Worksheets("DailyRecord").Cells(2, 4)
>> > If skipcell.Value > 0 Then
>> >    Sheets("DailyRecord").Select
>> >    Rows("2:2").Select
>> >    Selection.Insert Shift:=xlDown
>> >    Selection.ClearFormats
>> >    End If
>> > Next counter
>> >
>> > -- 
>> > BillR
>> >
>> >
>> > "JLGWhiz" wrote:
>> >
>> >> It would be helpful if you post the code you have so far.  It helps to
>> >> fill
>> >> in the gaps in your narrative.
>> >>
>> >>
>> >> "BillR" <BillR@discussions.microsoft.com> wrote in message
>> >> news:DCB74376-35D4-4426-8DFC-23B2565A9323@microsoft.com...
>> >> >I want to do something like this:
>> >> > Select a sheet
>> >> > for row=3 to 17
>> >> > select cells A:F
>> >> > copy the cells
>> >> > Select A2 on another sheet
>> >> > Paste what I copied.
>> >> > Shift down 1 row
>> >> > next row.
>> >> >
>> >> > I have verything down pretty well except selecting A:F for each row 
>> >> > as
>> >> > it
>> >> > comes up.
>> >> > I would appreciate any help you can give me.
>> >> > Thanks.
>> >> > -- 
>> >> > BillR
>> >>
>> >>
>> >> .
>> >>
>>
>>
>> .
>> 


0
JLGWhiz
4/15/2010 5:50:07 PM
Reply:

Similar Artilces:

deleting multiple rows
Hello: 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. A B 1 Sat 2 Sun 3 Mon 4 Tue 5........etc. I don't know where to start. Thanks, Wes Hi Wes, Depending on what you wish to do, XL's built in WeekNum function may assist. --- Regards, Norman "Wes" <additude@texstapes.com> wrote in message news:uV1CDQweFHA.3836@tk2msftngp13.phx.gbl... >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 jrw562 wrote: > > 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. -- Dave Peterson ...

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 With Worksheets(WS) 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)) ...

Cell selection
Hi, 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 Jason Hi, 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 Cheers, Shane Devenshire "Boenerge" wrote: > Hi, > 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 > Jason I am having this p...

Select a certain number of cells in a row
Hi, 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? Regards, Valerie maybe... dim myRng as range dim myCol as long with activesheet mycol = activecell.Column set myrng = .range(.cells(5,mycol),.cells(10,mycol)) end with myrng.select ======= or with activesheet .cells(5,activecell.column).resize(6).select end with 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. -- Duane Hookom Microsoft Access MVP "SheldonHinds" wrote: > 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? Cheers, Thomas ...

Combining Pivot Tables
Hi All, 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? Thanks, Alan. "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 "Clearcreek" wrote: > 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 Regards, Emece.- --Copy range --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 --------------- Jacob Skaria "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: ' OLD myChart.SetSourceData Source:=Sheets("Main Sheet").Range( _ "DF71:IS71,DF73:IS73"), PlotBy:=xlRows ' NEW 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.Range(myWs.Cells(Date_Week.row, FirstDataCol.Column), 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 note') insert into #temp (itemnum, firstname, notes) values (1, 'keith', 'some other note') 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 way round. However, it relies on cell A1 being available to create the -1 in the clipboard. 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 ? Sub Paste_Minus() ' 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. Hi Paul Maybe something like this =IF(B1="","",IF(B1>0,B1+A1)) Regards Cimjet "Paul Kaye" <paulmjkaye@gmail.com> wrote in message news:05befaf3-9ba8-48c8-aebb-654f0269d1dc@34g2000hsf.googlegroups.com... > 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 ?? -- KlK, MCSE I think xl2k and xl2002 have gotten better with this. But I don't have xl97 anymore, so these are guesses. How about... Select B1:K1 (or whatever row) edit|clear|formatting if that f...

trying to select the last 3 digits of a field
hello all this is the query im using (I am looking to get the last 3 characters of a field): rep_code: Right(tablename.fieldname,3) And I get this error: IDBC--call failed [Informix][Infomirx ODBC Driver][Informix]A syntax error has occurred. (#-201) 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)? Any ideas??? Thanks ...

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. me.controlname.setfocus or in macro GoToControl "controlname" Bonnie http://www.dataplus-svc.com michelle wrote: >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 http://www.accessmonster.com/Uwe/Forums.aspx/access/200912/1 Or, if you don't want to use events, simply set your tab order from the form design view. -- Frank H 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?? Andrew 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 referring...

Autofit rows
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? Mike you may be able to use the advance filter where can select unique records only. 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 ...