PDA

View Full Version : am I seeing pastespecial limitations?



bigdoggit
02-25-2006, 07:26 PM
I have a spreadsheet I'm designing for finances. The piece I'm currently working on is a procedure that will delete reciepts from a sheet. Once the information is cleared, I want to fill in the empty rows that are left. So if the sheet has reciepts 1-4, organized numerically with reciept 1 first, reciept 4 last, and I delete reciepts 2 and 3, there will be two empty rows between reciept 1 and reciept 4. I want to keep the information together, so I want to move reciept 4 into where reciept 2 was. This needs to be dynamic of course since I can't predict which reciepts will be removed and when. I have everything worked out except the pastespecial feature. After setting a named range to include only rows with reciept information, I copy it. From here on out, I hit walls. I tried copying the data to the beginning of my reciept list, but the copy and past fields overlap since reciept 1 has info in it, and excel doesn't like that. I next tried to clear the reciept information, and then copy, but the contents of the clipboard seem to be empty. Perhaps I need to paste immediately after copying. That is my best guess. And here I am, stuck. Sorry for the winded explanation. I just want my problem to make sense. The full code for this procedure is below. I hope this post isn't too long. I'm new to this.
To all those who help - my tired brain says thank you!

P.S. - my first question is, do I have to use PasteSpecial right after copying to the clipboard.
my second question is, if I can't, how should I do this copy without copying info to some temporary range, then clearing information, then copying again
my last question is, if anyone sees something I can write better - rock on, let me know. I just want to learn


Option Explicit
Private ChooseRecieptToDeleteTempRange As Range
Private NewRecieptsListRange As Range
Private NewRecieptsListRange2 As Range
Private BuildingRecieptsList As Range
Private celltolookafter As Range
Private rngareapart As Range
Private NewReciept As Range
Private iNewRecieptNumberofAreas As Integer
Private NewRecieptAreas() As Range
Private NewRecieptRow As Integer
Private NewRecieptColumn As Integer
Private RecieptToDeleteRowsAnalyzed As Integer
Private OriginalNumberOfRows As Integer
Private UnionRecieptArea As Range
Private vBuildingRecieptsList As Variant
Private Sub UserForm_Initialize()
'disabling events because direct editing of reciept information
'is prohibited. Can only be done through other procedures.
'This is to prevent accidental destruction of sensitive
'information
Application.EnableEvents = False
Dim RecieptName As Range
'activating worksheet containing master reciept information
Range("RecieptNumberMaster").Parent.Activate
'gathering reciept names from master sheet so user can choose
'from a listbox in a user form
Set ChooseRecieptToDeleteTempRange = _
Range("RecieptNumberMaster").ColumnDifferences(Range("RecieptNumberMaster").Rows(1))
'setting the gathered information as the source for the list box
ChooseRecieptToDeleteListBox.RowSource = ChooseRecieptToDeleteTempRange.Address
End Sub
'this code is utilized when users have just one reciept to delete
'and choose to double click it
Private Sub ChooseRecieptToDeleteListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ChosenReciept As String
Dim ChosenRecieptCell As Range
'setting ChosenReciept variable value as that of the reciept
'the user double clicked
ChosenReciept = ChooseRecieptToDeleteListBox.Value
'finding the reciept name chosen by user on reciept master sheet
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
'selecting reciept to delete so user can see what is happening
ChosenRecieptCell.Select
'gathering row number to be used for clearing row of information
ChosenRecieptCellRow = ChosenRecieptCell.Row
Unload Me
'clearing all information for that reciept
'but leaving the reciept name behind
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
'copying all data below deleted reciept to empty row
'keeping reciept information together
Range(Cells(ChosenRecieptCellRow + 1, 2), Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Copy _
Destination:=Cells(ChosenRecieptCellRow, 2)
'Deleting last reciept name in list since all information
'was moved up one row and that reciept name currently has
'no information in it
Range(Cells(Range("RecieptNumberMaster").Rows.Count, 1), _
Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Delete
'turing on events so user cannot edit master sheet directly
Application.EnableEvents = True
End Sub
Private Sub cmd_DeleteSelectedReciepts_Click()
Dim ChosenReciept As String
Dim SelectedReciept As String
Dim ChosenRecieptCell As Range
Dim iAvailableReciepts As Long
Dim iNewRecieptAreaPart As Integer
Dim RecieptArea As Variant
'using for loop to cycle through reciepts in list box
For iAvailableReciepts = 0 To (ChooseRecieptToDeleteTempRange.Rows.Count - 1)
'using if statement to find reciepts selected for deletion
If ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts) = True Then
ChosenReciept = ChooseRecieptToDeleteListBox.List(iAvailableReciepts)
'looking for selected reciepts name on master sheet
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
'row number to be used in upcoming clear statement
ChosenRecieptCellRow = ChosenRecieptCell.Row
ChosenRecieptCell.Select
'clearing contents of selected reciept
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
End If
Next
Unload Me
'setting new range that covers initial reciept area
Set NewRecieptsListRange = Range(Cells(Range("RecieptNumberMaster").Row, 2), _
Cells(Range("RecieptNumberMaster").Rows.Count, Range("A1").End(xlToRight).Column))
NewRecieptsListRange.Select
'setting the variable cell to be used in find statement
Set celltolookafter = Cells(Range("RecieptNumberMaster").Rows(1).Row, Range("A1").End(xlToRight).Column)
'setting range variable to be used for piecing together
'range based on what reciepts are left
Set BuildingRecieptsList = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, _
searchdirection:=xlNext, MatchCase:=False)
LineContinueSearching:
'finding first cell with information
Set NewRecieptsListRange2 = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, _
LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, _
searchdirection:=xlNext, MatchCase:=False)
'selecting row of the cell that contains information
Set NewRecieptsListRange2 = Range(Cells(NewRecieptsListRange2.Row, _
2), Cells(NewRecieptsListRange2.Row, _
Range("A1").End(xlToRight).Column))
NewRecieptsListRange2.Select
'using union command to add ranges together so new range
'can be pieced together row by row
Set BuildingRecieptsList = Application.Union(BuildingRecieptsList, _
NewRecieptsListRange2)
'checking to see if current row is last row in initial reciept
'range
If NewRecieptsListRange2.Row = NewRecieptsListRange.Rows.Count + 1 Then
'skipping to section for copying new range over old one
GoTo LineCopyNewRecieptsList
End If
'setting cell to look after as last one in the row just analyzed
Set celltolookafter = Cells(NewRecieptsListRange2.Row, _
NewRecieptsListRange2.Columns.Count + 1)
GoTo LineContinueSearching
LineCopyNewRecieptsList:
'copying new reciept list
BuildingRecieptsList.Copy
'clearing old reciept information
Range(Cells(Range("RecieptNumberMaster").Offset(1, 1).Row, 2), Cells(Range("RecieptNumberMaster").Rows.Count + 1, _
Range("A1").End(xlToRight).Column)).Clear
'pasting new information
'********Here is Problem - won't paste***********
'********Will be adding code to remove old reciept names that
'are no longer valid because of delted information***********
NewRecieptsListRange.Offset(1, 0).PasteSpecial
'turing on events so user can't directly edit master sheet
Application.EnableEvents = True
End Sub
Private Sub ChooseRecieptCancel_Click()
Unload Me
Application.EnableEvents = True
End Sub

XLGibbs
02-25-2006, 07:55 PM
Not sure what is going on with the VBA tags in that code...I tried editing it to make it readable...

Without even looking at your code, paste special will fail depending on certain circumstances such as pasting to/from merged cells, trying to copy and paste selections that are not contiguous...

Could you post a sanitized version of the workbook to check out?

I am not sure I even follow your description without seeing what is supposed to happen and the named ranges that are being referred to.

XLGibbs
02-25-2006, 08:00 PM
One way to avoid the issue is setting references to the objects in memory rather than just using range copy and paste...

You could do say...


Set RngToCopy = BuildingReceiptsList

'code to clear cells which would cancel the copy mode in your code above...

NewReceiptsListRange.Resize(RngtoCopy.Rows.Count,RngToCopy.Columns.Count).V alue = RngToCopy.Value


By setting the object (and its contained values) into memory, you can avoid the issue of needing to paste it somewhere right away...

Hope that helps. I will take a closer look at the code in a bit as it seems someone was able to clean up the VBA tag issue.

I tried editing the syntax but the VBA tags are acting goofy since the update....I am sure the Admins are on the case..

bigdoggit
02-25-2006, 09:39 PM
Whoops on the tags. Those things make sense to the writer, but I'm still learning how to break the communication barrier on stuff like that. There are probably variables declared and not used, other than that, hopefully this code makes some sense. I've only been messing with VBA (programming for that matter) for about two weeks, so I'm probably doing a lot the wrong or long way. I looked at the idea of saving the values and object to memory. I haven't tried it yet, will soon. One thing though, whenever I used the Count property for rows, it only saw one, even though the code would be selecting two different rows or reciepts, the row for reciept 1 and the row for reciept 4. That makes me wonder if it will copy all the values, or just the values of the first contiguous area, Reciept 1, and skip Reciept 4. I'll play though tomorrow at least. Thank you for mentioning that clearing the range loses the copy. I was wondering if that was the case, just couldn't find any clear documentation to convince me.

See one thing that is a problem too, I have named ranges on the Worksheet, and this might not be clear to everyone else. "RecieptNumberMaster" is a named range. In it are reciept names, like Reciept 1, Reciept 2, ect.

Also, I have as column headings:

Reciept #
Date
Company
Part Of Larger Reciept
UtilitiesAmount
Reciept #
Date
Company
Part Of Larger Reciept
UtilitiesAmount
Reciept#: Date: Company: Part Of Larger Reciept: Total Bill: Utilities: Food

This helps decide how many columns my NewRecieptsListRange is later in the code. Hopefully this helps.


Option Explicit
Private ChooseRecieptToDeleteTempRange As Range
Private NewRecieptsListRange As Range
Private NewRecieptsListRange2 As Range
Private BuildingRecieptsList As Range
Private celltolookafter As Range
Private rngareapart As Range
Private NewReciept As Range
Private iNewRecieptNumberofAreas As Integer
Private NewRecieptAreas() As Range
Private NewRecieptRow As Integer
Private NewRecieptColumn As Integer
Private RecieptToDeleteRowsAnalyzed As Integer
Private OriginalNumberOfRows As Integer
Private UnionRecieptArea As Range
Private vBuildingRecieptsList As Variant

Private Sub UserForm_Initialize()
Application.EnableEvents = False

Dim RecieptName As Range
Range("RecieptNumberMaster").Parent.Activate

Set ChooseRecieptToDeleteTempRange = Range("RecieptNumberMaster").ColumnDifferences(Range("RecieptNumberMaster").Rows(1))
ChooseRecieptToDeleteListBox.RowSource = ChooseRecieptToDeleteTempRange.Address
End Sub

Private Sub ChooseRecieptToDeleteListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ChosenReciept As String
Dim ChosenRecieptCell As Range
ChosenReciept = ChooseRecieptToDeleteListBox.Value
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
ChosenRecieptCell.Select
ChosenRecieptCellRow = ChosenRecieptCell.Row
Unload Me
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
Range(Cells(ChosenRecieptCellRow + 1, 2), Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Copy _
Destination:=Cells(ChosenRecieptCellRow, 2)
Range(Cells(Range("RecieptNumberMaster").Rows.Count, 1), Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Delete
Application.EnableEvents = True
End Sub

Private Sub cmd_DeleteSelectedReciepts_Click()
Dim ChosenReciept As String
Dim SelectedReciept As String
Dim ChosenRecieptCell As Range
Dim iAvailableReciepts As Long
Dim iNewRecieptAreaPart As Integer
Dim RecieptArea As Variant
For iAvailableReciepts = 0 To (ChooseRecieptToDeleteTempRange.Rows.Count - 1)
If ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts) = True Then
ChosenReciept = ChooseRecieptToDeleteListBox.List(iAvailableReciepts)
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
ChosenRecieptCellRow = ChosenRecieptCell.Row
ChosenRecieptCell.Select
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
End If
Next
Unload Me

Set NewRecieptsListRange = Range(Cells(Range("RecieptNumberMaster").Row, 2), Cells(Range("RecieptNumberMaster").Rows.Count, Range("A1").End(xlToRight).Column))
NewRecieptsListRange.Select
Set celltolookafter = Cells(Range("RecieptNumberMaster").Rows(1).Row, Range("A1").End(xlToRight).Column)
Set BuildingRecieptsList = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, searchdirection:=xlNext, MatchCase:=False)

LineContinueSearching:
Set NewRecieptsListRange2 = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, searchdirection:=xlNext, MatchCase:=False)
Set NewRecieptsListRange2 = Range(Cells(NewRecieptsListRange2.Row, 2), Cells(NewRecieptsListRange2.Row, Range("A1").End(xlToRight).Column))
NewRecieptsListRange2.Select
Set BuildingRecieptsList = Application.Union(BuildingRecieptsList, NewRecieptsListRange2)
If NewRecieptsListRange2.Row = NewRecieptsListRange.Rows.Count + 1 Then
GoTo LineCopyNewRecieptsList
End If

Set celltolookafter = Cells(NewRecieptsListRange2.Row, NewRecieptsListRange2.Columns.Count + 1)
GoTo LineContinueSearching



LineCopyNewRecieptsList:
BuildingRecieptsList.Copy
Range(Cells(Range("RecieptNumberMaster").Offset(1, 1).Row, 2), Cells(Range("RecieptNumberMaster").Rows.Count + 1, Range("A1").End(xlToRight).Column)).Clear
NewRecieptsListRange.Offset(1, 0).PasteSpecial
Application.EnableEvents = True
End Sub

Private Sub ChooseRecieptCancel_Click()
Unload Me
Application.EnableEvents = True
End Sub

bigdoggit
02-25-2006, 09:42 PM
Oh, quick question, how can I post the workbook. There is size limitation of 244. something kilobytes. No way can I fit mine into that. I figure I'm missing something

Bigdoggit

bigdoggit
02-25-2006, 09:44 PM
okay, last quick reply. The named range is defined with the following code:
=OFFSET(Sheet1!$A$2,0,0,MATCH("*",Sheet1!$A:$A,-1)

XLGibbs
02-26-2006, 06:34 AM
Post a sanitized version of the workbook without all the rows, just a sample of the data layout with a few rows. If possible included a "before", "after" and description of what happens to supplement the above.

I am pretty sure the method I described above would work for the place where you said your code fails though...

bigdoggit
02-26-2006, 07:23 PM
I tired the code, but unfortunately it didn't paste data into the range. It might have pasted emtpy cells. The code didn't fail though. I created a sanitzed workbook. I included the named range and the same sample data rows and columns I'm using. I'm also pasting the code I have with your suggested fix. I declared the rngtocopy as a Range. Didn't know what else it should be. Should I be creating a data object? Anyhow, I'll check back. Thanks.




Option Explicit
Private ChooseRecieptToDeleteTempRange As Range
Private NewRecieptsListRange As Range
Private NewRecieptsListRange2 As Range
Private BuildingRecieptsList As Range
Private celltolookafter As Range
Private rngareapart As Range
Private NewReciept As Range
Private iNewRecieptNumberofAreas As Integer
Private NewRecieptAreas() As Range
Private NewRecieptRow As Integer
Private NewRecieptColumn As Integer
Private RecieptToDeleteRowsAnalyzed As Integer
Private OriginalNumberOfRows As Integer
Private UnionRecieptArea As Range
Private vBuildingRecieptsList As Variant
Private RngToCopy As Range

Private Sub UserForm_Initialize()
Application.EnableEvents = False

Dim RecieptName As Range
Range("RecieptNumberMaster").Parent.Activate

Set BuildingRecieptsList = Range("A1")
Set ChooseRecieptToDeleteTempRange = Range("RecieptNumberMaster").ColumnDifferences(Range("RecieptNumberMaster").Rows(1))
ChooseRecieptToDeleteListBox.RowSource = ChooseRecieptToDeleteTempRange.Address
End Sub

Private Sub ChooseRecieptToDeleteListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ChosenReciept As String
Dim ChosenRecieptCell As Range
ChosenReciept = ChooseRecieptToDeleteListBox.Value
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
ChosenRecieptCell.Select
ChosenRecieptCellRow = ChosenRecieptCell.Row
Unload Me
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
Range(Cells(ChosenRecieptCellRow + 1, 2), Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Copy _
Destination:=Cells(ChosenRecieptCellRow, 2)
Range(Cells(Range("RecieptNumberMaster").Rows.Count, 1), Cells(Range("RecieptNumberMaster").Rows.Count, 255)).Delete
Application.EnableEvents = True
End Sub

Private Sub cmd_DeleteSelectedReciepts_Click()
Dim ChosenReciept As String
Dim SelectedReciept As String
Dim ChosenRecieptCell As Range
Dim iAvailableReciepts As Long
Dim iNewRecieptAreaPart As Integer
Dim RecieptArea As Variant
For iAvailableReciepts = 0 To (ChooseRecieptToDeleteTempRange.Rows.Count - 1)
If ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts) = True Then
ChosenReciept = ChooseRecieptToDeleteListBox.List(iAvailableReciepts)
Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
ChosenRecieptCellRow = ChosenRecieptCell.Row
ChosenRecieptCell.Select
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear
End If
Next
Unload Me

Set NewRecieptsListRange = Range(Cells(Range("RecieptNumberMaster").Row, 2), Cells(Range("RecieptNumberMaster").Rows.Count, Range("A1").End(xlToRight).Column))
NewRecieptsListRange.Select
Set celltolookafter = Cells(Range("RecieptNumberMaster").Rows(1).Row, Range("A1").End(xlToRight).Column)
Set BuildingRecieptsList = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, searchdirection:=xlNext, MatchCase:=False)

LineContinueSearching:
Set NewRecieptsListRange2 = NewRecieptsListRange.Find(what:="*", after:=celltolookafter, LookIn:=xlValues, lookat:=xlPart, searchorder:=xlRows, searchdirection:=xlNext, MatchCase:=False)
Set NewRecieptsListRange2 = Range(Cells(NewRecieptsListRange2.Row, 2), Cells(NewRecieptsListRange2.Row, Range("A1").End(xlToRight).Column))
NewRecieptsListRange2.Select
Set BuildingRecieptsList = Application.Union(BuildingRecieptsList, NewRecieptsListRange2)
If NewRecieptsListRange2.Row = NewRecieptsListRange.Rows.Count + 1 Then
GoTo LineCopyNewRecieptsList
End If

Set celltolookafter = Cells(NewRecieptsListRange2.Row, NewRecieptsListRange2.Columns.Count + 1)
GoTo LineContinueSearching



LineCopyNewRecieptsList:
Set RngToCopy = BuildingRecieptsList
Range(Cells(Range("RecieptNumberMaster").Offset(1, 1).Row, 2), Cells(Range("RecieptNumberMaster").Rows.Count + 1, Range("A1").End(xlToRight).Column)).Clear
NewRecieptsListRange.Resize(RngToCopy.Rows.Count, RngToCopy.Columns.Count).Value = RngToCopy.Value
Application.EnableEvents = True
End Sub

Private Sub ChooseRecieptCancel_Click()
Unload Me
Application.EnableEvents = True
End Sub

XLGibbs
02-26-2006, 07:30 PM
I will snoop around in there and see what I can figure out....I think I understand what you are doing...

XLGibbs
02-26-2006, 07:32 PM
Is BuildingRecieptList a named range?

If so Set rngtoCopy = Range("BuildingReceiptList")

otherwise it is essentially setting the object to nothing...which may be the issue.

bigdoggit
02-26-2006, 08:59 PM
nah, buildingrecieptlist is declared and set in the procedure. It is defined by adding rows using the union command so I can create a range of all the rows that have reciept info left after clearing contents from reciepts chosen for removal.

I did a little extra work with your code, and decided to not clear contents of my reciepts before reinputting the data.

this is a quick explanation of what I coded. If it is confusing, skip it:
This is confusing to say, but after I remove the reciepts chosen for such action, I am building a range that selects all reciepts left, then am attempting to move this range to the beginning of my master sheet reciepts list. This is my way of getting rid of empty space rows left by clearing contents of reciepts chosen for deletion. I then attempt to clear all data from my master sheet, except the reciept names, such as reciept 1, reciept2, etc. These are essentially a series I add to as more reciepts are put into the spreadsheet. Okay, so anyhow, then I try putting that new range of only rows with info at the begging, inline with Reciept 1, and let it fill the rest of the reciept rows.

Now I'm not sure that above paragraph makes sense, so if it doesn't, this is what I wanted to pass on anyway. When I decide not to delete the the contents of buildingrecieptslist, which only includes rows that have reciept info in them, and then use the command you sent me
NewRecieptsListRange.Resize(RngToCopy.Rows.Count,

RngToCopy.Columns.Count).Value = RngToCopy.Value

it only pastes on row of the two rows selected. I had used the count method before during troubleshooting and found it only counted one row, not the two that exist. I think that is why only one row is put in with your suggested code. Anyway, wanted to pass that on. You may have already realized this by now, but just in case. Thanks for taking on the case. Young grasshopper appreciates it.

bigdoggit
02-26-2006, 09:00 PM
nah, buildingrecieptlist is declared and set in the procedure. It is defined by adding rows using the union command so I can create a range of all the rows that have reciept info left after clearing contents from reciepts chosen for removal.

I did a little extra work with your code, and decided to not clear contents of my reciepts before reinputting the data.

this is a quick explanation of what I coded. If it is confusing, skip it:
This is confusing to say, but after I remove the reciepts chosen for such action, I am building a range that selects all reciepts left, then am attempting to move this range to the beginning of my master sheet reciepts list. This is my way of getting rid of empty space rows left by clearing contents of reciepts chosen for deletion. I then attempt to clear all data from my master sheet, except the reciept names, such as reciept 1, reciept2, etc. These are essentially a series I add to as more reciepts are put into the spreadsheet. Okay, so anyhow, then I try putting that new range of only rows with info at the begging, inline with Reciept 1, and let it fill the rest of the reciept rows.

Now I'm not sure that above paragraph makes sense, so if it doesn't, this is what I wanted to pass on anyway. When I decide not to delete the the contents of buildingrecieptslist, which only includes rows that have reciept info in them, and then use the command you sent me
NewRecieptsListRange.Resize(RngToCopy.Rows.Count,

RngToCopy.Columns.Count).Value = RngToCopy.Value

it only pastes on row of the two rows selected. I had used the count method before during troubleshooting and found it only counted one row, not the two that exist. I think that is why only one row is put in with your suggested code. Anyway, wanted to pass that on. You may have already realized this by now, but just in case. Thanks for taking on the case. Young grasshopper appreciates it.

bigdoggit
02-26-2006, 09:01 PM
okay, that post really wasn't thrilling enough for a repeat oncore. whoops

XLGibbs
02-26-2006, 09:05 PM
Well if it is worth saying, say it twice in the words of MWE of these boards.

The resize code was(silly me) only counting COLUMNS. Regardless, it should have looked more like this.

Edit...wait, that is what it looked like....not sure why it was only copying 1 row, if there were to in RngToCopy but my head hurts. Twice.




VBA:

RngDestination.Resize(RngtoCopy.Rows.Count,RngToCopy.Columns.Count).Value = RngToCopy.Value

VBA tags courtesy of www.thecodenet.com (http://www.thecodenet.com)

bigdoggit
02-27-2006, 02:27 PM
Okay, I attached a spreadsheet with the before rows of info, how it looks after removing information i want to remove, and a short description of what I need to do next, but can't make happen.

I think I need to simplify my question so it isn't so convoluted. I need to have code that will identify empty rows of info in between rows that do contain info, then move all information to beginning of area so no spaces in between. Hopefully the attached sheet will make that clearer yet. The sample code you give doesn't work because the range I have only counts 1 row, not 2. I am using the union command to build this temporary range, and I'm not sure, but since the different areas aren't contiguous, I don't think the count property reflects accurately. Check out the new attachment, and add code to count how many rows are in the temporary range - you'll see what I mean I hope. In the code I passed on, the temp range is BuildingRecieptsList. Hope you see what I've been missing.

XLGibbs
02-27-2006, 03:06 PM
Seems like copying the non blank rows into the old location is doing more than necessary. Based on your attachment, it seems like you could just go through the rows, find empy cells in column B and delete the row, shifting other cells up?

There is a lot going on there, most of which I am unclear on as to it's necessity.

When you clear the data from the cells, why not just to ..

.entirerow.delete Shift:=xlup

Without being able to step through your code, it is very difficult to simply add a line or edit one line to correct the error. The logic error could be in the named range, the looping through the cells, the Find("*")...

I wish I could make more sense of it than I am, but just seeing the data sample, and looking at the amount of code written and not being able to test it makes it more difficult.

This line in the code:

ChosenRecieptCellRow = ChosenRecieptCell.Row
ChosenRecieptCell.Select
Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).Clear

which appears to clear that data from the row, could be

Range(Cells(ChosenRecieptCellRow, 2), Cells(ChosenRecieptCellRow, 255)).EntireRow.Delete Shift:=xlup

Which would seem to achieve the same goal without having to go back and deal with the temporary range copy and pastes. Following the code actions as best I can by reading it, there may be ways to really streamline it...

bigdoggit
02-27-2006, 04:34 PM
Can you just put the information in a spreadsheet and copy the code into a module to test it? I did try deleting at first and shifting rows up. It is more straightforward, but everytime a row was deleted, it took long enough for an hourglass to appear, and I really want it quicker than that. I did have the whole row selected, and assume it will be quicker when I'm just choosing enough columns to cover the actual information instead of all 255. I keep thinking since I've only been working with VBA for a couple weeks, I'm not familiar with a command or a method that just does this very quickly and simply. I'm trying to prepare for extremes. After this spreadsheet grows for a few years, there will be a lot of receipts. If I choose to clean house and delete several for some reason, I don't want it to take a long time, especially if I really max out my categories (which are the column headings) and get detailed. By categories, I mean Food Bill, Utilties, Car Payments, etc. Now, I noticed that I can copy and paste the info into another destination, then clear the original range with its spaces from deleted receitps, and finally repaste the information back again. This would probably involve have a hidden sheet to paste data to and from. That may be my only other option except delete. Does the delete command seem slow when you use it? Is it somewhat slower compared to other commands? Sorry to put you through the headaches. I may just have to compromise somewhere. But where there's a will .........

XLGibbs
02-27-2006, 04:59 PM
Can you just put the information in a spreadsheet and copy the code into a module to test it?

No, I cant paste your code in and test it on 4 rows of data.


I did try deleting at first and shifting rows up. It is more straightforward, but everytime a row was deleted, it took long enough for an hourglass to appear, and I really want it quicker than that. I did have the whole row selected, and assume it will be quicker when I'm just choosing enough columns to cover the actual information instead of all 255.


Deleting a row should take no time at all. It does not matter how many columns are selected. Deleting empty rows is faster than copying/paste/clear/copy/paste.

You should have: Application.EnableEvents = False and Application.Calculation = xlManual and Application.screenupdating = false

at the beginning of your code, otherwise, more is happening than needs to during the operation.



I keep thinking since I've only been working with VBA for a couple weeks, I'm not familiar with a command or a method that just does this very quickly and simply. I'm trying to prepare for extremes. After this spreadsheet grows for a few years, there will be a lot of receipts.

all the more reason to allow the help offered and sanitize some sample data so i can work with the file as it is currently set up.



If I choose to clean house and delete several for some reason, I don't want it to take a long time, especially if I really max out my categories (which are the column headings) and get detailed. By categories, I mean Food Bill, Utilties, Car Payments, etc. Now, I noticed that I can copy and paste the info into another destination, then clear the original range with its spaces from deleted receitps, and finally repaste the information back again. This would probably involve have a hidden sheet to paste data to and from.

That is patently inefficient.



That may be my only other option except delete. Does the delete command seem slow when you use it? Is it somewhat slower compared to other commands? Sorry to put you through the headaches. I may just have to compromise somewhere. But where there's a will .........

there always other options.

I have written code that can do 10 times the amount of work yours is intended and it works in no time flat. I am fortunate that I have more experience than you is all, and have learned from the some very talented people along the way.

Sanitize the data and or email me the workbook, I do this kind of thing all the time for people, and it is no burden or headache. I wouldn't offer to help if i thought it would be too difficult or troublesome.

let me know.

bigdoggit
02-27-2006, 07:19 PM
first question, what do you mean by sanitize? Secondly, what do I email too? I appreciate you taking the time. I hadn't added the tidbits to turn off events, or calculation, or the screenupdating. That should definitely help. Anyhow, I'll check back.

I thought by sanitze you meant take out the vba tags, and keep the data simple.

XLGibbs
02-27-2006, 07:22 PM
Email it to my email address. It should be linked to the email icon in my posts.

By sanitize I mean remove any sensitive data if you like. I will send you a private message with my email to be sure.

bigdoggit
02-28-2006, 03:42 PM
Okay, I just tried the delete command again - and again, it is very slow. I'm stepping through the code. and when I hit that code, my god is it slow! Even if I compile the code first. And if I run the code, the screen just hangs for a while. I'm doing something very wrong here! :banghead: Anyhow, I'm going to do some reading for a while on stuff and take a break. Please let me know what is going on with delete.

XLGibbs
02-28-2006, 04:32 PM
Hi bigdoggit, I received and cleaned up your file best I could, although i was still unable to test it. Possible you have a version newer than mine and I may not have the .columnDifferences argument available...

Nonetheless, I removed what was superfluous, and changed the delete routine to be able to receive the chose receipt from your list box based on either double click of the list box item or by multi select and hitting your button.

The delete was likely slow due to the conglomeration of a lot of simultaneous events that may not have had to happen. In the revised code below, the screenupdating and events are disabled in a custom function and only within the delete routine.


Private Sub UserForm_Initialize()

Dim RecieptName As Range
Range("RecieptNumberMaster").Parent.Activate

Set ChooseRecieptToDeleteTempRange = Range("RecieptNumberMaster").ColumnDifferences(Range("RecieptNumberMaster").Rows(1))
ChooseRecieptToDeleteListBox.RowSource = ChooseRecieptToDeleteTempRange.Address
End Sub

Private Sub ChooseRecieptToDeleteListBox_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ChosenReciept As String
Dim iAvailableReciepts As Long

For iAvailableReciepts = 0 To ChooseRecieptToDeleteListBox.ListCount
If ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts) = True Then
ChosenReciept = ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts)
Call RecieptDelete(ChosenReceipt)
End If
Next

Call RecieptDelete(ChosenReceipt)

End Sub

Private Sub cmd_DeleteSelectedReciepts_Click()
Dim ChosenReciept As String
Dim SelectedReciept As String
Dim ChosenRecieptCell As Range
Dim iAvailableReciepts As Long
Dim iNewRecieptAreaPart As Integer
Dim RecieptArea As Variant

For iAvailableReciepts = 0 To ChooseRecieptToDeleteListBox.ListCount

If ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts) = True Then
ChosenReciept = ChooseRecieptToDeleteListBox.Selected(iAvailableReciepts)

Call RecieptDelete(ChosenReceipt)
End If
Next

LineContinueSearching:


LineCopyNewRecieptsList:

End Sub

Private Sub ChooseRecieptCancel_Click()
Unload Me
End Sub



Above is the revised userform code with much removed since it did not appear necessary at this time to achieve the desired results. I did not copy in the private declarations of your variables ...although they are in the workbook I sent back to you.

Below is the regular module

Option Explicit

Sub RecieptDelete(ByVal ChosenReceipt As String)
Dim Response As Integer
Dim ResponseOther As String
Dim RecieptToFix As Range
Call ToggleStuff
Response = MsgBox("Would you like to delete " & _
ChosenReceipt & "?", vbYesNoCancel)
If Response = vbCancel Then Exit Sub
If Response = vbNo Then GoTo LineResponseOther

Dim ChosenRecieptCell As Range

ChosenReciept = Range("RecieptNumberMaster").Rows(Selection.Row - 1).Value

Set ChosenRecieptCell = Range("RecieptNumberMaster") _
.Find(what:=ChosenReciept, LookIn:=xlValues, lookat:=xlWhole)
ChosenRecieptCell.EntireRow.Delete
Call ToggleStuff
Exit Sub

LineResponseOther:
Call ToggleStuff

ChooseRecieptToDeleteUserform.Show
End Sub
Sub ToggleStuff()
Application.ScreenUpdating = Not Application.ScreenUpdating
Application.EnableEvents = Not Application.EnableEvents
End Sub


End Sub


Hope it helps out, sorry I was unable to test it to make sure it was working properly, but it "should" be okay. But I may have made a mistake absent testing....

XLGibbs
02-28-2006, 04:33 PM
ooops... I just now noticed a line that will botch the routine..
Delete the below line from the regular module

ChosenReciept = Range("RecieptNumberMaster").Rows(Selection.Row - 1).Value