vba code to copy all cells in a variable range

A

andycreber

Board Regular
Joined
May 20, 2010
Messages
74
Office Version
  1. 2016
Hi all

I have the following code in bold are copying cells that return data in formula after cell I4, but it is copying cells with a formula in it even if the formula is not returning any data, so visually the cells appear to be blank. IE my has to go down to 25 rows but in various cases the copied data may only apply to 5 rows.

It there a way to only copy the returned data in the formula? IE 5 rows without removing the formulas on the source sheet for the blank data

Reason being when I paste special values in the "Dump" sheet it is not finding the last true blank cell, its finding the whole copied range IE 20 rows so when this happens with multiple sheets there are blank rows in my data that cannot be sorted to align the rows.

All help would be greatly appreciated

thanks MR Excel helpers


Sheets("FL").Select
Range("I4").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Dump").Select
Range("B5000").End(xlUp).Select
ActiveSheet.Paste
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
 
Sort by date Sort by votes
it said false

I have several formulas sourcing a value, so my formulas on this test return 2 vaules in cells j4 and j5 then under that i have the same formulas relating to cell j6 through to j43, the cells j6 - j43 are blank as i have not values to pull through which is correct in this instance, but when the code does the copy and paste special values it copies the formulas and put a space in the destination worksheet ("Dump") and thus creates cells with a space in below may last data that was pasted

does that make sense?
 
Upvote 0

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
If you run the code below (on a copy of your workbook) does the formula change to TRUE?

Code: 
Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set myRng = Sheets("Dump").Range("B:C")
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart

        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                               myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub
 
Upvote 0
MARK858 said:
If you run the code below (on a copy of your workbook) does the formula change to TRUE?

Code: 
Sub Trimit()
    Dim myCell As Range, myRng As Range
    
    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Set myRng = Sheets("Dump").Range("B:C")
    
    With myRng
        .Replace What:=Chr(160), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13) & Chr(10), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(13), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(21), Replacement:=Chr(32), LookAt:=xlPart

        .Replace What:=Chr(8), Replacement:=Chr(32), LookAt:=xlPart
        .Replace What:=Chr(9), Replacement:=Chr(32), LookAt:=xlPart
    End With

    On Error Resume Next
    For Each myCell In Intersect(myRng, _
                               myRng.SpecialCells(xlConstants, xlTextValues))
        myCell.Value = Application.Trim(myCell.Value)
    Next myCell
    On Error GoTo 0

    With Application
        .Calculation = xlCalculationAutomatic
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
End Sub
Click to expand...

no, it still stays as false

unless i am doing it wrong:

what i did was type your formula on the ("Dump") sheet in the blank cells referencing the source of the sheet.

=isblank(FL!F7)

The F7 cell on the FL sheet is blank with a formula in it not returning a vlaue (which is correct in this case)

thanks for help help again, i appreciate it
 
Upvote 0
result of my vba code is thus:

the row item numbers for 11 & 12 should be inline with the bold descriptions which are the corresponding items but as you can see there are skipped rows due to my code putting in a blank space in those cells due to the formulas

96.7331/08/2018DOLE AS
106.7331/08/2018DOLE FL
31/08/2018NOAH FL
31/08/2018MR
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
11923.6031/08/2018
12229.954545531/08/2018

<tbody>
</tbody>
 
Last edited:
Upvote 0
Hi Mark858

I found a similar post from a user who had the same issue as me, his code below is, but I don't know how to incorporate into my code, would you know how?

Many thanks


New Member<dl class="userinfo_extra" style="margin: 5px 0px; float: left; width: 180px; height: auto !important;"><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Join Date</dt><dd style="margin: 0px; padding: 0px 0px 3px;">Jun 2016</dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Posts</dt><dd style="margin: 0px; padding: 0px 0px 3px;">8</dd><dd class="collapse" style="margin: 0px; padding: 0px 0px 3px; font-weight: bold; clear: both;">Post Thanks / Like </dd><dd style="margin: 0px; padding: 0px 0px 3px;"></dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Mentioned</dt><dd style="margin: 0px; padding: 0px 0px 3px;">0 Post(s)</dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Tagged</dt><dd style="margin: 0px; padding: 0px 0px 3px;">0 Thread(s)</dd></dl>

[h=2]
icon1.png
Re: Macro to copy paste special ignoring formulas in cells[/h]
Cracked it Mark, thankyou

Its a different solution to only selecting the cells with true values in to copy but it (Clean Cells) works.
I can now do away with the sort.

Many Thanks

Sub Macro1()
'
' Macro1 Macro
'


'


Dim NextRow As Range
With Sheets("Data")
Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
End With
Sheets("Receipt").Range("o14:ab31").Copy
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing

Dim rng As Range, cell As Range
Set rng = Worksheets("Data").Columns("B").SpecialCells(xlCellTypeConstants)


For Each cell In rng.Cells
cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
Next cell



End Sub​


Last edited by geminian; Jun 12th, 2016 at 06:30 PM.​














andycreber said:
result of my vba code is thus:

the row item numbers for 11 & 12 should be inline with the bold descriptions which are the corresponding items but as you can see there are skipped rows due to my code putting in a blank space in those cells due to the formulas

96.7331/08/2018DOLE AS
106.7331/08/2018DOLE FL
31/08/2018NOAH FL
31/08/2018MR
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
31/08/2018
11923.6031/08/2018
12229.954545531/08/2018

<tbody>
</tbody>
Click to expand...

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">New Member<dl class="userinfo_extra" style="margin: 5px 0px; float: left; width: 180px; height: auto !important;"><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Join Date</dt><dd style="margin: 0px; padding: 0px 0px 3px;">Jun 2016</dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Posts</dt><dd style="margin: 0px; padding: 0px 0px 3px;">8</dd><dd class="collapse" style="margin: 0px; padding: 0px 0px 3px; font-weight: bold; clear: both;">Post Thanks / Like </dd><dd style="margin: 0px; padding: 0px 0px 3px;"></dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Mentioned</dt><dd style="margin: 0px; padding: 0px 0px 3px;">0 Post(s)</dd><dt style="margin: 0px 10px 0px 0px; padding: 0px; float: left; min-width: 60px; width: auto !important;">Tagged</dt><dd style="margin: 0px; padding: 0px 0px 3px;">0 Thread(s)</dd></dl>

[h=2]
icon1.png
Re: Macro to copy paste special ignoring formulas in cells[/h]
Cracked it Mark, thankyou

Its a different solution to only selecting the cells with true values in to copy but it (Clean Cells) works.
I can now do away with the sort.

Many Thanks

Sub Macro1()
'
' Macro1 Macro
'


'


Dim NextRow As Range
With Sheets("Data")
Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
End With
Sheets("Receipt").Range("o14:ab31").Copy
NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
Application.CutCopyMode = False
Set NextRow = Nothing

Dim rng As Range, cell As Range
Set rng = Worksheets("Data").Columns("B").SpecialCells(xlCellTypeConstants)


For Each cell In rng.Cells
cell.Value = Application.WorksheetFunction.Clean(Trim(cell.Value))
Next cell



End Sub​


Last edited by geminian; Jun 12th, 2016 at 06:30 PM.​

</body>
 
Upvote 0
andycreber said:
no, it still stays as false

unless i am doing it wrong:

what i did was type your formula on the ("Dump") sheet in the blank cells referencing the source of the sheet.

=isblank(FL!F7)

The F7 cell on the FL sheet is blank with a formula in it not returning a vlaue (which is correct in this case)

thanks for help help again, i appreciate it
Click to expand...

No I didn't ask you to change the formula, it is the cell on the cell on the DUMP SHEET that is blank after the paste we want, the one that showed FALSE previously we need referencing in the formula.
We cannot alter anything on the FL sheet, we are looking to cleanup the result on the Dump sheet.
 
Last edited:
Upvote 0
MARK858 said:
No I didn't ask you to change the formula, it is the cell on the cell on the DUMP SHEET that is blank after the paste we want, the one that showed FALSE previously we need referencing in the formula.
We cannot alter anything on the FL sheet, we are looking to cleanup the result on the Dump sheet.
Click to expand...


Hi Mark858,

Sorry I messed it up, when i run the Trimit maro the cell now changes to TRUE
 
Last edited:
Upvote 0
That's ok, now are there any cells in Dump columns C:D that are now blank that need to remain blank?
 
Upvote 0
FYI If i run my original code the formula states is as FALSE
When I run the TRIMit code it changes it to TRUE

there are blank cells in there between my data in columns B&C

So I still need to remove the blank cells between the data
 
Upvote 0
Sorry my typo I meant columns B:C so the question should have read...
That's ok, now are there any cells in Dump columns B:C that are now blank that need to remain blank?
Click to expand...

You don't need to tell me that there are blank cells that removing (I already know that :biggrin:), you need to tell me if there are any blank cells in the whole columns that don't need removing.
 
Upvote 0
You must log in or register to reply here.

Similar threads

excelbytes
  • Solved
VBA Code To Copy Data Into Next Blank Column
Replies
5
Views
73
mumps
mumps
D
  • Question
VBA optimization advice
Replies
3
Views
93
bobsan42
bobsan42
N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
182
DanteAmor
DanteAmor
willow1985
  • Solved
Find first blank cell in column A in a Table - Runtime error 91
Replies
4
Views
98
willow1985
willow1985
S
  • Solved
Copy/Paste cell if checkbox selected
Replies
4
Views
294
kevin9999
K

Forum statistics

Threads
1,214,830
Messages
6,121,839
Members
449,051
Latest member
excelquestion515

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top