The workbook contains 2 sheets i.e., Raw data and Allocation sheet.
This is a chargeback allocation that I need to allocate to the team. My query is that... There is a column called "site" (Column L). That needs to be split according to the number I enter in the "Allocation" sheet. For instance, if the total data contains 400 rows and I need a macro where data should split based on the "site" column i.e., HYD 100 and BLR 200. So, whatever number I enter in the "Allocation" sheet should break accordingly, and the remaining data can be deleted.
Please review and do needful. I really appreciate any help you can provide.
Assumes both sites exist...As per sample file requirement...Keep 63 BLR and keep 64 HYD if that much exists...If not...leave as is
Change red snippet to Delete after testing to see if code works...
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Hi PCI,
It means if the data is 300 and I need only 200 i.e., BLR 100 and HYD 100 so remaining 100 can be deleted. So, basically whatever the number I enter should only display in Raw data.
Thanks for looking into the query.
I guess the code was not working fine. It was highlighting with yellow some data but it's not splitting the data according to the number I enter in sheet 2 ("Allocation"). Can you please review it and advise with the new/modified code?
a) You already commented on the number of rows to delete but: And what rows should be deleted? What is the elimination criteria?...
b) It seems to me that the best thing would be for you to take the workbook from Post #1 and add a third sheet in which you show us the expected final result...
You are always very welcome if you add reputation by clicking the * (bottom left) of each message that has helped you.
I guess the code was not working fine. It was highlighting with yellow some data but it's not splitting the data according to the number I enter in sheet 2 ("Allocation").
I purposely did that and explained above why...The yellow highlighted is the data that will be deleted keeping only the required allotted lines...
I wanted you to first see if this is what you were wanting...If so, you had to change the highlight code snippet to delete, which will leave only the allotted lines remaining...
Change this...
By doing this...Only the allotted lines that you stipulated would remain...The rest would be deleted...
If this is not what you wanted, then I suggest, as per beyond Excel, uploading a sample file showing a before | after expected scenario so that we can see exactly what it is you would want to achieve...
Option Explicit
Sub allocate()
Dim lr&, i&, cell As Range, BLRcount&, HYDcount&, site
With Sheets("Allocation")
BLRcount = .Range("C4")
HYDcount = .Range("D4")
End With
With Sheets("Raw Data")
lr = .Cells(Rows.Count, "L").End(xlUp).Row ' last row with data in column L
site = .Range("L2:L" & lr).Value ' store column L value into array
For i = 1 To UBound(site) ' loop thru column L value, and subtract from total till 0, then delete value
Select Case site(i, 1)
Case Is = "BLR"
If BLRcount = 0 Then
site(i, 1) = ""
Else
BLRcount = BLRcount - 1
End If
Case Is = "HYD"
If HYDcount = 0 Then
site(i, 1) = ""
Else
HYDcount = HYDcount - 1
End If
End Select
Next
.Range("L2:L" & lr).Value = site ' copy back site into sheet
.Range("L2:L" & lr).SpecialCells(xlBlanks).EntireRow.Delete ' delete blank cells in bulk
End With
End Sub
I need a macro where data should split based on the "site" column i.e., HYD 100 and BLR 200. So, whatever number I enter in the "Allocation" sheet should break accordingly, and the remaining data can be deleted.
You didn't tell us about remaining data for other than HYD/BLR if there are any.
Simply
It's my bad...I was not able to understand that before. Very sorry for that. Yea the macro was working fine now. I replaced the code "If X2 - X1 + 1 > Num Then .Rows(X1 + Num & ":" & X2).Interior.Color = vbYellow" with "If X2 - X1 + 1 > Num Then .Rows(X1 + Num & ":" & X2).Delete". This was too perfectly working and meeting all my requirements. Thanks a lot for your quick assistance.
Thanks a lot for looking into the query and this code was perfect. Correctly working on the data I enter without any churns. Thank you once again for your assistance.
Thanks for looking into the query. No, there is no need to have the deleted data. So requested the macro to eliminate the data apart from the number I entered in the table. However, the query got resolved from the above codes mentioned. Thanks for the time for looking into the query.
Thank You all for your great help. All the codes mentioned by you were working fine.
Surprisingly, I found many people here to help people with their queries...
Thanks a lot for everyone for your great assistance on queries.
It's my bad...I was not able to understand that before. This was too perfectly working and meeting all my requirements. Thanks a lot for your quick assistance.
Glad you got it sorted...Tx for rep...Happy Coding...
LinkBack URL
About LinkBacks
Need assistance on Macro (splitting the data)
Register To Reply
Originally Posted by Pawan Sai
Bookmarks