Forum Discussion
Solved
help with deleting all rows but header when column has a cell that contains 0
I have many cells that have a zero in them in column D and I want to delete the entire row if there is a 0 in column D, but the data size will change so I need it to also not be conditional on the exact amount of rows. I have found a VBA for this but there's much in terms of excel script to do it and I need it to be a office script and I can't do a filter and delete because then it deletes the column header which I need for the data and only deletes the amount of rows on that worksheet so I can't use it for other worksheets that have more or less rows. this is what I have this so far but it just returns errors and the other one I had was just deleting the entire workbook. right now with the script below it first had an error in line 17 about needing a ) so I added it where I thought it went and now it's saying argument expression expected, so I'm not sure what it wants. I really appreciate anyone who tries to assist me.
Thank you so much for the help!
function main(workbook: ExcelScript.Workbook) {
const sheet = workbook
.getActiveWorksheet();
const dataSize = sheet
.getRange("D:D")
.getUsedRange()
.getRowCount() - 1
let selectedSheet = workbook.getActiveWorksheet();
// Toggle auto filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getRange());
// Apply values filter on selectedSheet
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
// Delete all cells on selectedSheet
selectedSheet.getRange("D2").delete(
.getRange(dataSize - 1, 0))(ExcelScript.DeleteShiftDirection.up);
}
I guess I tried to answer in another your post https://techcommunity.microsoft.com/t5/excel/auto-fill-in-office-script-api/m-p/3915532#M203345
4 Replies
I guess I tried to answer in another your post https://techcommunity.microsoft.com/t5/excel/auto-fill-in-office-script-api/m-p/3915532#M203345
I cannot comment on VBA scripts, so I'm leaving all of that for somebody else. I want to ask a "big picture" question or two, if I might.
Your description is a bit confusing: you talk, for example, of wanting to delete an entire row if column D contains a zero but not being able to do filter and delete because that deletes the column. (It is entirely possible to delete rows that have been filtered, without deleting the column; hence that particular confusion).
A deeper issue, however, is what I refer to as the "bigger picture" here. If we were sitting down together, face-to-face, I would definitely, as a responsible "consultant," want to know what this spreadsheet is about. I'd need to go over with you what kind of data you're tracking, in what sort of "business"--not necessarily meaning commercial pursuit, just what is the context, the bigger purpose?
The reason for needing to know that: in general--not always, but in general--once data are collected toward some purpose, it's better for the sake of data integrity to retain the data. It might make sense to mark rows as "defunct" or "no longer applicable" but they're there as part of the history of whatever it is you're tracking.
For example, I was director of the HR/Payroll database for a major corporation. Employees often changed jobs, were promoted, transferred, put on leave, returned....but the history of each position they occupied was kept permanently, even when a new position or location superseded the prior.
Presumably, in your situation, there are data elements in columns other than 😧 by deleting the row, you're removing that part of the historical record, whatever it is that the row represents.
As noted, this may be entirely legitimate in your case, but I think it's important to ask, as one of the "consultants" you've invited to take a look at your project. So, could you provide a glimpse into that bigger picture?
And, if it's possible without divulging confidential information, if you could post a copy of the workbook on OneDrive or GoogleDrive, with a link pasted here that grants access, that too would help us help you.
- It was phone reports where the rows that had a 0 in them were calls that were dropped/ didn't get through because it was ringing to all phones in the ring group. it truly was unneeded data that needs to be deleted so that the spreadsheets are smaller because they will all be combined into one huge spreadsheet for the year to be created into a report. My question was answered and I did get help with fixing the code but thank you for your kind reply and help.
blairwebb ,you are welcome, thank you for the clarification.
