| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to make this code smaller
Hi,
I've got this which works fine. Set sh1 = ActiveWorkbook.Sheets("Data") Set sh2 = ActiveWorkbook.Sheets("UK") 'H1 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next 'H2 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H2" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next 'H3 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H3" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next But as I'm looking at the same data in Sh1. Can this be made shorter by some sort of Or here If c.Value = "H1" Then Something like if c.Value = "H1","H2","H3" then? Thanks Carl |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to make this code smaller
If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then
Regards, Stefi €˛Carlos€¯ ezt Ć*rta: Hi, I've got this which works fine. Set sh1 = ActiveWorkbook.Sheets("Data") Set sh2 = ActiveWorkbook.Sheets("UK") 'H1 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next 'H2 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H2" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next 'H3 For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H3" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Select Rows(lr1 + 1).Select ActiveSheet.Paste End If Next But as I'm looking at the same data in Sh1. Can this be made shorter by some sort of Or here If c.Value = "H1" Then Something like if c.Value = "H1","H2","H3" then? Thanks Carl |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to make this code smaller
Untested:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Rows(lr1 + 1) End If Next or:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then c.EntireRow.Cut Sheets("UK").Rows(sh2.Range("A" & Rows.Count).End(xlUp).Row + 1) Next Result won't necessarily be in the same order. In both snippets I've tried shortening you cut/paste - again untested. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131534 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to make this code smaller
Thanks to all for replying.
Stefi's worked first time and tidied it up to where I needed it to be. Many thanks Carl "p45cal" wrote: Untested:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Rows(lr1 + 1) End If Next or:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then c.EntireRow.Cut Sheets("UK").Rows(sh2.Range("A" & Rows.Count).End(xlUp).Row + 1) Next Result won't necessarily be in the same order. In both snippets I've tried shortening you cut/paste - again untested. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131534 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
How to make this code smaller
NOT the most efficient way but whatever makes you happy.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Carlos" wrote in message ... Thanks to all for replying. Stefi's worked first time and tidied it up to where I needed it to be. Many thanks Carl "p45cal" wrote: Untested:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then lr1 = sh2.Range("A" & Rows.Count).End(xlUp).Row c.EntireRow.Cut Sheets("UK").Rows(lr1 + 1) End If Next or:For Each c In sh1.Range("A1:A" & lr2) If c.Value = "H1" Or c.Value = "H2" Or c.Value = "H3" Then c.EntireRow.Cut Sheets("UK").Rows(sh2.Range("A" & Rows.Count).End(xlUp).Row + 1) Next Result won't necessarily be in the same order. In both snippets I've tried shortening you cut/paste - again untested. -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=131534 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do you make cells smaller? | Charts and Charting in Excel | |||
| Make ribbon Icons smaller | Excel Discussion (Misc queries) | |||
| How do I size a worksheet ? ( make smaller ) | Excel Discussion (Misc queries) | |||
| How to split worksheet to make smaller | Excel Worksheet Functions | |||
| Make Values Smaller Q | Excel Worksheet Functions | |||
Hybrid Mode
