Expanding selected ranges that are changing next time (with vba).

  • Thread starter Thread starter emil
  • Start date Start date
E

emil

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
 
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
 
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
 
Sorry
Don’t 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???
 
Back
Top