Delete Last Filled Row (columns A To H)
Jan 30, 2009
I use a macro to copy some data from a .csv file. The data is copied to columns A to H (starting from row 31), the number of rows filled depends on the particular case and is not fixed. The first column gets filled with the serial numbers. the problem is that in the last row cells of columns B to H contain three dashes (---).
I have written a simple code that finds the last filled cells in column A. After having found this row, I would like to clear the cells or delete them. the below mentioned simple code does finds the last filled row but I am not able to find a command to delete or clear the cells of this row.
View 3 Replies
ADVERTISEMENT
Oct 12, 2008
vba to auto delete cells which is filled with color. e.g if from c39 to d39 is filled with lavender i want a vba to delete cell c39 to h39 until all cells with lavender is deleted. If there are other threads like this please direct me.
0.00 00 0.00 00 0.00 00 0.00 00 0.00 00 0.00 00 0.00 00
View 9 Replies
View Related
Aug 10, 2014
I have this formula:
=DATEDIF(C3,I3,"MD")
However i need that formula to be blank if columns C and I are not filled out yet
View 2 Replies
View Related
Jul 23, 2014
I have two columns that are the same field, both Middle Name. The first column does not have every single row filled and the second column has every row the first column does not have and vice versa. I want to merge them to one filled column.
View 2 Replies
View Related
Oct 29, 2013
I created a lab result page for long term patients. thus, they may have 1 to 1000 lab results. so i need to create an infinite print range, but just printing the filled ones.
Right after column J, print 2nd page if filled, if not, dont. And printing A1:J6 to every page is a good solution to not to lose data in long term.
[URL]
View 3 Replies
View Related
Sep 8, 2008
Really need a formula for a traffic light system
ie
if 3 colums have entries no
4 Maybee
5 yes
and it highlight it in green amber or yellow
View 11 Replies
View Related
Dec 19, 2012
I have this data lets say id denotes a particular house and the multiple entries of the same house are for different members of a house. Now i want to calculate the total no of people who earn in a particular household i.e if a cell is filled or not.
idB C
00118000040000
001
001
001
002170000
[code].....
View 2 Replies
View Related
Dec 9, 2013
I would like to create 384 by 2 table such as below. One column has series of alphabets (A-P) using 24 cells for each letter and other column has series of number (1-24) for each corresponding letter on left column.
A
1
A
2
A
3
...
...
A
24
B
1
B
2
...
...
P
23
P
24
I tried my best using my limited knowledge of VBA but macro fails by overwriting entire column in each loop (This code is for only one column, but I want to make two columns shown above).
Code:
Sub alphabetNumber()
Dim e As Long, f As Long
For f = 1 To 16
For e = 4 To 387 Step 24
Range(Cells(e, 2), Cells(e + 23, 2)) = Chr(64 + f)
Next e
Next f
End Sub
View 9 Replies
View Related
Mar 6, 2014
I'd like a code to automatically delete the value in column AK and its corespondent in AL, if the value in AK is filled in column C
View 7 Replies
View Related
Jun 24, 2009
I would like a macro to find the columns named "apple" and "peach" and delete them. These would always be in row 1 but would always be in different column letters which is why I want the macro to simply find these columns by their name and not by their column letter.
And yes, I do mean the entire column altogether, shifting entire columns to the left. Wipe it off the face of the earth
View 4 Replies
View Related
Jul 15, 2009
1. Remove J,K,N,A Columns,
2. In the last O (TIMESTAMP) column, the date is 14-Jul-09 format change it to 07/14/2009 (this format mm/dd/yyy
3.Filter L column (VAL_INLAKH) Remove all rows from whole sheet which has 0 value
4. Column C (EXPIRY_DT) date format is 24-Sep-09 , "dd-Sep-09" change to "Sep" only
5.Merge Column B,C,D,E (SYMBOL.EXPIRY_DT.STRIKE_PR.OPTION_TYP
respectively )
View 3 Replies
View Related
May 22, 2008
Is there a limit on the number of rows and columns that can be deleted in a macro on Excel 2003? I am trying to create a macro that, amoung other things, delets 1119 rows and 54 columns. If I delete the columns first, the rows will not delete. If I delete the columns first, the rows will not delete.
View 12 Replies
View Related
Sep 25, 2007
I had wanted to go through my spreadsheet and concatenate two columns (A & B)into one (A) then delete the duplicate column (B), but have found no way to do that. Now I am trying to search then insert a column prior to the other two, concatenate the data into the new column then delete the columns. I am specifically having a problem with my Range statement and can't figure out how to activate it or discern it after using the Find command.
Sub GroupGender()
Cells.Find(What:="Group", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False _
, SearchFormat:=False).Activate
Selection.EntireColumn.Insert Shift:=xlToRight
With Range("a1", Cells(Rows.Count, 1).End(xlUp))
.Offset(0, 0) = "=RC[1] & "" "" & RC[2]"
.Offset(0, 2) = .Offset(4, 2).Value
End With
Cells.Select
Selection.Replace What:="Group Sex", Replacement:="Grp/Sx", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Range("A1").Select.......................
View 4 Replies
View Related
Oct 24, 2012
Using the following code to remove empty rows based on whether a specific range of columns is empty. The code works if the cell has a zero, but not when the cell is blank. An example of the data is attached.
VB:
Public Sub DelRows2()
Dim Cel As Range, searchStr, FirstCell As String
Dim searchRange As Range, DeleteRange As Range
[Code].....
View 1 Replies
View Related
Feb 18, 2006
Im trying to delete the next 5 columns in a spreadsheet whenever a specific cell value = 0 and for it to repeat to the end of the sheet.
Example:
If cell b5 = 0 then delete the next 5 columns, i've tried a couple variations, but it deletes all the 0 values in other rows.
View 14 Replies
View Related
Aug 12, 2014
I'm prompting the user for what two ranges they want to keep in a excel sheet and then I want to delete the rest of the columns. There may be 5 total columns and there may be 30, it will vary. The reason I want to do this is because I will then save data to CSV file and it can only have two columns of data to be passed on for other data processing.
View 5 Replies
View Related
Aug 13, 2014
I'm looking for the correct way of deleting columns based on if row 2 has an x in it..
I have two versions that I tried but I am pretty sure there are faster ways of doing it, I don't quite know how to delete all the columns at once.
[Code] ......
The first version doesn't work for some reason and the second column works but is a slow loop, what to do to make this faster?
View 12 Replies
View Related
Oct 18, 2009
I have this file where i delete columns which are extra, in my real file most of the cells are formulas or links . Basically i need a macro which looks in row 4, and if it finds any zeros ( number 0 ) in the cell it deletes that whole column.
The zero is a indicator for me when i work on these files if it is needed or not. Included the file as an attachement.
View 2 Replies
View Related
Feb 13, 2012
How do I read in an entire row and then delete the columns that are not flagged with some value.
Example: Row 1 has a value in column A, C, D, F
I want to delete columns B, E, G+
Note: The value could be numerical or it could be a text string.
View 9 Replies
View Related
Feb 29, 2012
I have around 2368 rows for in each column and I have around 8 columns and what I need to do is to remove any gaps. I do not know how to attach picture here, but I can explaining it in words.
A1: 0.9
A2:
A3:
A4:
A5: -0.09
A6:
A7: 0.4
Is there a way to eliminate those gaps (A2, A3, A4, A6...) in one go?
View 9 Replies
View Related
Sep 18, 2012
I am looking for vba or formula can delete all columns in spreadsheet except for certain columns let's say I want to say delete all except for columns a, d, f
View 5 Replies
View Related
Aug 19, 2013
I'm having some problem with a part of my code that deletes entire columns. With ActiveCell as my reference, I want to delete 'Abs_Diff' Columns to the left hand side of the ActiveCell column, including the ActiveCell column. 'Abs_Diff' is a variable of integer type.
SO if ActiveCell column is "P" and 'Abs_Diff'=2, then delete Columns "O:P"
if ActiveCell column is "P" and 'Abs_Diff'=4, then delete Columns "M:P"
My code is selecting columns incorrectly, maybe due to merged cells in cols A,B,C or something..not sure.
Here is my code:
Code:
'DELETE COLUMNS
Set StartPoint = ActiveCell
For X = 1 To Abs_Diff
StartPoint.EntireColumn.Delete
Set StartPoint = ActiveCell.Offset(0, -1)
Next X
View 1 Replies
View Related
Mar 29, 2014
I am attempting to delete rows with columns not containing "MC". In the example below, the result should be 3 rows remaining (1,2 and 5):
Y
Z
AA
IP/DA/SV
MC/SWL/SA
MC/SH/SA
[code]...
Instead, all I get are results with Y starting in MC, in this case the last row, all the other rows are deleted. I need to get all rows that have MC in three columns (Y, Z or AA) and delete the ones that do not. In this case, rows 3 & 4.
Below is the code I have been using:
Sub Del_Rows()
Application.ScreenUpdating = False
With Range("Y1:AA1", Range("Y" & Rows.Count).End(xlUp))
.AutoFilter Field:=1, Criteria1:="*MC*"
.Offset(1).EntireRow.Delete
.AutoFilter
End With
Application.ScreenUpdating = True
End Sub
Seems simple enough but this newbie chump is stumped why it is not reading the other columns
View 4 Replies
View Related
Jan 22, 2009
-- I have one sheet with 100 columns. (Master)
-- I have a report that only needs 10 of these columns, in a certain order. (Report)
What is the most efficient way to do this? Note I am not a VBA guru.
Can I add the column headers and positions (in Master) to an array?
Then in a new sheet (Report), copy and paste the desired columns.
View 9 Replies
View Related
Jan 12, 2007
I tried to modify this code for my next problem: to delete entire columns where the cell in row 2 is empty, but it doesnt work. The code I use is:
Sub test2()
'This code will create a copy of the current sheet, and retain only the rows
'that have the value '1' in column B.
ActiveSheet.Copy Before:=ActiveSheet
Application. ScreenUpdating = False
On Error Resume Next
Dim ir As Long, mrows As Long, lastcell As Range
Set lastcell = Cells.SpecialCells(xlLastCell)
mrows = lastcell.Row
'Note rows are deleted from the bottom going up
For ir = mrows To 1 Step -1
If Len(Trim(Range("b" & ir).Value)) = 0 Then
Rows(ir).Delete Shift:=xlUp
End If
Next
Application.ScreenUpdating = True
End Sub
The ideal solution would be not a second macro but one that does firstly delete the rows and then the columns.
View 8 Replies
View Related
Jul 24, 2007
I need code that will check each column from D:P in the " Total" row for a value of Zero. If it finds a zero then delete entire column. This will be the final procedure of a macro that does other formatting things so the number of rows will differ causing the "Total" row to be dynamic. I have the following code that will find the word total, but I don't know how to set the range to offset 1 col each time checking for zero and deleting if true. The word "Total" is merged between columns A:B. see example sheet.
Sub asdf()
Dim c As Variant
Dim Rng As Range
'search the sheet for "Total"
Set c = Cells.Find("Total", lookat:=xlWhole)
'if it is found
If Not c Is Nothing Then
Set Rng = Range(c, c.Offset(0, 3))
View 5 Replies
View Related
Sep 7, 2007
I have written a complex macro but i would like to delete some of the columns from my spreadsheet using the macro? I can highlight the column but then dont know what next to write to get the column deleted.
View 4 Replies
View Related
Oct 10, 2007
In a sheet where the longest column is A, I want to delete the rows between the last value in Column A and the last value in Column C.
View 9 Replies
View Related
Mar 4, 2008
Is there a way to delete x # of rows in columns b, c, d, g, j, m, p & s.
I regularly enter data in these rows for comparisons and then need to delete the data with out deleting the formulas in e, h, k, n, q & t.
A macro or one key then delete would be great.
View 4 Replies
View Related
May 6, 2008
What is the fastest way to delete 2 consecutive columns after skipping 2 columns?
For eg: Column A Column B Column C Column D Column E Column F
I would like to delete column A & B, skip Column C & D, and then delete Column E & F
View 4 Replies
View Related
