Define range of columns to copy

[fari1]

hi, below is the part of a code, this line of code copies the entire row and paste in sheet2(i-e wsdst) what i want is that the code to copy range A to Z instead of entire row.

If Not rngAll Is Nothing Then rngAll.EntireRow.Copy wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1)

 

[fari1]

any help

 

[mirabeau]

Untested, but how about?

If Not rngAll Is Nothing Then rngAll.EntireRow.Resize(, 26).Copy wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1)

 

[fari1]

rngAll.EntireRow.Resize(, 26).Copy wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1)


this part of the code, it is highlighting, and giving application defined error

 

[mirabeau]

Run this bit of code, just to give you an idea of what I was thinking.

Range("C5").EntireRow.Resize(, 5).Select

Without knowing more about the context/details of your code I can't really say any more.

 

[fari1]

just to u give u an idea, this is my complete code

Sub findTEXT()
    
    Static wsSrc As Worksheet: Set wsSrc = ActiveWorkbook.Sheets("Sheet2")
    Static wsDst As Worksheet: Set wsDst = ActiveWorkbook.Sheets("Sheet3")
    
    Static StartAddress As String
    Dim rngFnd As Range
    Dim rngAll As Range
    Dim allfound As Boolean
    
    Set rngFnd = wsSrc.UsedRange.Find("consolidated")
    If Not rngFnd Is Nothing Then StartAddress = rngFnd.Address
    
    While Not rngFnd Is Nothing And allfound = False
        If RowLen(wsSrc, rngFnd) < 50 And rngAll Is Nothing Then Set rngAll = rngFnd
        Set rngFnd = wsSrc.UsedRange.Find("consolidated", rngFnd)
        If RowLen(wsSrc, rngFnd) < 50 Then
            If rngAll Is Nothing Then Set rngAll = rngFnd
            If Intersect(rngAll, rngFnd) Is Nothing _
            And Intersect(rngAll.EntireRow, rngFnd) Is Nothing _
            Then Set rngAll = Union(rngAll, rngFnd)
        End If
        If rngFnd.Address = StartAddress Then allfound = True
    Wend
If Not rngAll Is Nothing Then rngAll.EntireRow.Copy wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1)
wsDst.Range("AA" & wsDst.UsedRange.Offset(, 1).Find(what:="*", after:=wsDst.[B1], searchdirection:=xlPrevious).Row + 1).Value = wsSrc.Range("A1").Value
End Sub
Function RowLen(ws As Worksheet, rng As Range) As Long
    
    Dim c As Range
    For Each c In Intersect(rng.EntireRow, ws.UsedRange)
        RowLen = RowLen + Len(c.Text)
    Next c
    
End Function

and this part of the code i want to change to bring the row after resize, so that i may have the blank column A

If Not rngAll Is Nothing Then rngAll.EntireRow.Copy wsDst.Cells(Rows.Count, "B").End(xlUp).Offset(1)

 

[Weaver]

replace

rngAll.EntireRow.copy

with

intersect(rngAll.EntireRow,rngall.worksheet.range("A:Z")).copy

 

[fari1]

yes working, thanks so much