Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expanding selected ranges that are changing next time (with vba).
Hi
Can someone help me? In e. g.Range ("G1:G700") I am selected e. g. Range ("G3:G5") .Select and I expanded this selection with €œOffset€ function e. g. ActiveCell.Range("A1:A3").Select ActiveCell.Offset(11, 0).Range("A1:D3").Select Selection.Copy It is OK, but next time I have other selected range e. g. Range ("G20:G30") and the precedent €œOffset€ is not OK. What can I do? Thanks for your time. Emil |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expanding selected ranges that are changing next time (with vba).
You don't need to use select. The macro recorder uses select but it is
slower than directly addressing the range and makes the code harder to understand. Here is some ideas Set MyRange = Range("G1:G17) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row Set MyRange = Range("G1:G" & LastRow) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row for RowCount = 1 to LastRow step 5 'copy range from K to N 'for rows RowCount to RowCount + 4 Set MyRange= Range("K" & RowCount & ":N" & (RowCount + 4)) MyRange.Copy Next RowCount "emil" wrote: Hi Can someone help me? In e. g.Range ("G1:G700") I am selected e. g. Range ("G3:G5") .Select and I expanded this selection with €œOffset€ function e. g. ActiveCell.Range("A1:A3").Select ActiveCell.Offset(11, 0).Range("A1:D3").Select Selection.Copy It is OK, but next time I have other selected range e. g. Range ("G20:G30") and the precedent €œOffset€ is not OK. What can I do? Thanks for your time. Emil |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expanding selected ranges that are changing next time (with vb
Thank you for answer.
I must try it, but I think that I was not clear. At the next run, the program selects another cells. The procedure is more long, but I chose only a part to don't shall consume your time. After I try, if doesn't what I will, I shall send you an elder part from procedure. Don't want to disturb you overmuch. Apologize for my very poor English. Milion thanks Emil "joel" wrote: You don't need to use select. The macro recorder uses select but it is slower than directly addressing the range and makes the code harder to understand. Here is some ideas Set MyRange = Range("G1:G17) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row Set MyRange = Range("G1:G" & LastRow) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row for RowCount = 1 to LastRow step 5 'copy range from K to N 'for rows RowCount to RowCount + 4 Set MyRange= Range("K" & RowCount & ":N" & (RowCount + 4)) MyRange.Copy Next RowCount "emil" wrote: Hi Can someone help me? In e. g.Range ("G1:G700") I am selected e. g. Range ("G3:G5") .Select and I expanded this selection with €œOffset€ function e. g. ActiveCell.Range("A1:A3").Select ActiveCell.Offset(11, 0).Range("A1:D3").Select Selection.Copy It is OK, but next time I have other selected range e. g. Range ("G20:G30") and the precedent €œOffset€ is not OK. What can I do? Thanks for your time. Emil |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Expanding selected ranges that are changing next time (with vb
Sorry
Dont work why expected. Here is my Sheets ("Afec cu produse") (Range (E1:G38) ) - My Sheet has more much - D E F 1 =EXACT(F1;E1) AfecÅ£iune 2 FALSE COLITÄ‚ ABSORBIREA ÅžI ARDEREA GRÄ‚SIMILOR 3 FALSE COLITÄ‚ ABSORBÅ¢IA GRÄ‚SIMILOR 4 FALSE COLITÄ‚ ACCIDENT VASCULAR - TRATAMENT 5 FALSE COLITÄ‚ ACCIDENT VASCULAR (PREVENTIV) 6 FALSE COLITÄ‚ ACCIDENT VASCULAR (PREVENTIV) 7 FALSE COLITÄ‚ ACIDITATE LA STOMAC 8 FALSE COLITÄ‚ ACNEE 9 FALSE COLITÄ‚ ACNEE .... FALSE COLITÄ‚ ... .... FALSE COLITÄ‚ ... .... FALSE COLITÄ‚ ... 36 TRUE COLITÄ‚ COLITÄ‚ 37 TRUE COLITÄ‚ COLITÄ‚ 38 TRUE COLITÄ‚ COLITÄ‚ G 1 Produse 2 Advanced Fat Burners (250 tablete) 3 Fat Absorber (100 capsule) 4 Mega Protect 4 Life (90 tablete) 5 Cholestone (90 tablete) 6 Mega Protect 4 Life (90 tablete) 7 Green Care (240 tablete) 8 Acidophilus with Psylium (100 capsule) 9 AC-Zymes (100 capsule) .... ... .... ... .... ... 36 Digest Ease (100 tablete) 37 Liquid Clorophyll 38 Meal Time (100 tablete) 'In Range ("D1:D700") Step1, I have Formulas =EXACT(F1;E1) 'In Range ("D2:D700") Step2 Range("D2:D700").Select Application.CutCopyMode = False Selection.Copy Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'In Range ("D2:D700") Step3 Selection.Find(What:="true", After:=ActiveCell, LookIn:=xlValues, LookAt _ :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _ False, SearchFormat:=False).Activate 'In Range ("D2:D700") Step4 Selection.Replace What:="true", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False 'In Range ("D2:D700") Step5 Selection.SpecialCells(xlCellTypeConstants, 1).Select 'In Step5 ActiveCell.Range("A1:D3").Select Selection.Copy Windows("Program rec. Produse MANEVRÃ.xls").Activate ActiveCell.Select Selection.Insert Shift:=xlDown It is OK, bat if next time selections are not same??? "joel" wrote: You don't need to use select. The macro recorder uses select but it is slower than directly addressing the range and makes the code harder to understand. Here is some ideas Set MyRange = Range("G1:G17) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row Set MyRange = Range("G1:G" & LastRow) MyRange.Copy LastRow = Range("G" & Rows.Count).end(XLUP).Row for RowCount = 1 to LastRow step 5 'copy range from K to N 'for rows RowCount to RowCount + 4 Set MyRange= Range("K" & RowCount & ":N" & (RowCount + 4)) MyRange.Copy Next RowCount "emil" wrote: Hi Can someone help me? In e. g.Range ("G1:G700") I am selected e. g. Range ("G3:G5") .Select and I expanded this selection with €œOffset€ function e. g. ActiveCell.Range("A1:A3").Select ActiveCell.Offset(11, 0).Range("A1:D3").Select Selection.Copy It is OK, but next time I have other selected range e. g. Range ("G20:G30") and the precedent €œOffset€ is not OK. What can I do? Thanks for your time. Emil |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Expanding ranges in macros | Excel Discussion (Misc queries) | |||
Expanding selected data | Charts and Charting in Excel | |||
Expanding ranges causing probs? | Excel Programming | |||
Expanding ranges after sort??? | Excel Programming | |||
named ranges - changing ranges with month selected | Excel Programming |