|
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 |
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 |
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 |
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 |
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 |
How to make this code smaller
I'm sure it's not as it started with my code.. :-)
Will come back and took a look at your coding when I've got more time and knowledge to understand it. Thanks very much for your input. Still on a very stiff learning curve. Carl "Don Guillett" wrote: 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 |
| All times are GMT +1. The time now is 09:17 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com