Code to Resize a variable number of rows including target

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

I have the ?? hard coded in my macro but sometimes the number of rows to include vary.

I know I have hit all around it but can't nail the proper xldown.rows.count or Rows.Count).End(xlUp)scheme to replace the ??.

I thought for sure I would have it in my cheat-sheet but...

If Not aCell Is Nothing Then
aCell.Resize(??, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
End If

Thanks,
Howard
 
It occurs to me that being in For Each / Next worksheet statement it may be different than if a single sheet was the case.

So the variable rows code needs to key off the cell aCell on a particular worksheet.

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
Set aCell = ws.UsedRange.Find(what:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
aCell.Resize(5, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Set sName = ws.Range("XFD1")
MsgBox sName
End If

End If
Next '/ ws

Thanks.
Howard
 
I did solve my query. Here's the entire code.

Sub SheetsScan()
Dim ws As Worksheet
Dim strSearch As String
Dim aCell As Range
Dim sName As Variant
Dim lrC As Long

On Error GoTo Err

strSearch = InputBox("Find For This.", "Find This")

For Each ws In ActiveWorkbook.Worksheets
If ws.Name <> "Sheet1" Then
Set aCell = ws.UsedRange.Find(what:=strSearch, LookIn:=xlValues, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
lrC = aCell.End(xlDown).Row
aCell.Resize(lrC, 1).Copy Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp)(2)
Set sName = ws.Range("XFD1")
MsgBox sName
End If

End If
Next '/ ws
Exit Sub
Err:
MsgBox Err.Description
End Sub

Howard
 
You should be aware that the word "Err" is a keyword as in your line...

MsgBox Err.Description

...which refs the errors colection, and so I'm curious why you also use
it as a GoTo label. Better this way...

On Error GoTo ErrExit
'..code

NormalExit:
Exit Sub

ErrExit:
MsgBox Err.Description

...so your code is easier to understand what's going on. You might think
it's good for now but 6 months down the road will not recall your
reasoning. Also, another developer has to 'figure out' what you're
code's intent is. (Albeit this is fairly simple, bad habits can cause
greater problems!)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
You should be aware that the word "Err" is a keyword as in your line...



MsgBox Err.Description



..which refs the errors colection, and so I'm curious why you also use

it as a GoTo label. Better this way...



On Error GoTo ErrExit

'..code



NormalExit:

Exit Sub



ErrExit:

MsgBox Err.Description



..so your code is easier to understand what's going on. You might think

it's good for now but 6 months down the road will not recall your

reasoning. Also, another developer has to 'figure out' what you're

code's intent is. (Albeit this is fairly simple, bad habits can cause

greater problems!)
Click to expand...

Did not know that was a keyword. I was browsing for some example code and came across one that had a little of what I was looking for.

The err handler was part of that macro and I left it intact, and it was useful to a degree as I puttered with the code.

I'll either change as you suggest or eliminate it altogether.

Howard

Seemed like a good idea at the time so
 
Did not know that was a keyword. I was browsing for some example
code and came across one that had a little of what I was looking for.

The err handler was part of that macro and I left it intact, and it
was useful to a degree as I puttered with the code
Click to expand...

Well I'm sure VB[A] could easily figure out the procedure has a label
named "Err:", but it's just not good practice to use (or misuse)
built-in keywords that way. That's not much different than writing...

On Error GoTo Error
OR
On Error GoTo GoTo

...and should have been readily recognized as bad coding when you saw
the lines...

Err:
MsgBox Err.Description

...IMO!<g> That's the problem with using 'found code' *as is* without
revising it to your own standards or coding conventions (if you even
have those in place yet)!<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
..IMO!<g> That's the problem with using 'found code' *as is* without

revising it to your own standards or coding conventions (if you even

have those in place yet)!<g>
Click to expand...

<<revising it to your own standards or coding conventions>> Therein lies my problem. I have 'standards and conventions' they are sadly third rate.<g>

Kinda like biting the ears off the chocolate Easter bunnies and giving the rest to the kids.

Howard
 
<<revising it to your own standards or coding conventions>> Therein
lies my problem. I have 'standards and conventions' they are sadly
third rate.<g>
Click to expand...

Sorry about my not-so-subtle hint!<g> Though I'd call it 2nd or 3rd
hand, not "third rate"! Fact is, we all learn by colecting and studying
code samples/examples, but we realy don't begin to effectively apply
them to our projects until we revise them to our own 'style' (as it
were), if for no other reason than to further our own understanding of
how to best approach our projects!
Kinda like biting the ears off the chocolate Easter bunnies and
giving the rest to the kids.
Click to expand...

Ha, ha! I just ate the head (ears and all) off mine, and I'm saving the
rest for later...<bg>!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top