Related:
- Copy a range if the a specific column is blan
- How to find specific words on a page - Guide
- How to delete a column in word - Guide
- Insert a new sheet at the end of the tab names and paste the range names starting in cell a1. autofit columns a:b and name the worksheet as range names. ✓ - Excel Forum
- Apple airtag range - Guide
3 responses
Assumptions:
1. Column H does not have any blank cells so it will be used to stop the loop.
I tried this with test data based on your description.
Private Sub CommandButton1_Click()
Dim r
r = Range("A65536").End(xlUp).Row
Dim i
Dim j
j = 8
Do While Not IsEmpty(Range("H" & j))
If IsNumeric(Range("A" & j)) And Range("D" & j) = "" Then
r = r + 1
Range("A" & r) = Range("A" & j)
Range("B" & r) = Range("B" & j)
Range("C" & r) = Range("C" & j)
Range("D" & r) = Range("D" & j)
Range("E" & r) = Range("E" & j)
Range("F" & r) = Range("F" & j)
Range("G" & r) = Range("G" & j)
End If
j = j + 1
Loop
End Sub
1. Column H does not have any blank cells so it will be used to stop the loop.
I tried this with test data based on your description.
Private Sub CommandButton1_Click()
Dim r
r = Range("A65536").End(xlUp).Row
Dim i
Dim j
j = 8
Do While Not IsEmpty(Range("H" & j))
If IsNumeric(Range("A" & j)) And Range("D" & j) = "" Then
r = r + 1
Range("A" & r) = Range("A" & j)
Range("B" & r) = Range("B" & j)
Range("C" & r) = Range("C" & j)
Range("D" & r) = Range("D" & j)
Range("E" & r) = Range("E" & j)
Range("F" & r) = Range("F" & j)
Range("G" & r) = Range("G" & j)
End If
j = j + 1
Loop
End Sub
Tweedledum
Posts
181
Registration date
Monday January 19, 2009
Status
Member
Last seen
April 2, 2009
125
Jan 22, 2009 at 11:23 AM
Jan 22, 2009 at 11:23 AM
I dont actually get what you want, do you want to search til you find a blank row? if so its would be something like this
With ActiveSheet
LRow = .Cells(.Rows.Count, "G").End(xlUp).Row
that will count through the rows, sorry i dont have much time and i amn't a vba master but the XLup idea might get you on the right road, if you google it you will find others for going left right up down etc if i have time i might read through your post a but slower and try to see if there is an easy solution
With ActiveSheet
LRow = .Cells(.Rows.Count, "G").End(xlUp).Row
that will count through the rows, sorry i dont have much time and i amn't a vba master but the XLup idea might get you on the right road, if you google it you will find others for going left right up down etc if i have time i might read through your post a but slower and try to see if there is an easy solution
Hey!
Thanks for the help, but I'm really new at this so I'm not even sure where that code u replied with goes.
Just to clarify, I have a spreadsheet that I update daily that has data starting at A8 through column M. I want to look in column D for blank spaces if column A has an integer in it. If it does, I want to copy that row of data from column A to G to the bottom of the spreadsheet.
This coding does that, but instead of copy just columns A to G it's copying the entire row. This is causing the data in column H to M to disappear.
Does that help?
---again, thanks for the help!
Thanks for the help, but I'm really new at this so I'm not even sure where that code u replied with goes.
Just to clarify, I have a spreadsheet that I update daily that has data starting at A8 through column M. I want to look in column D for blank spaces if column A has an integer in it. If it does, I want to copy that row of data from column A to G to the bottom of the spreadsheet.
This coding does that, but instead of copy just columns A to G it's copying the entire row. This is causing the data in column H to M to disappear.
Does that help?
---again, thanks for the help!
Tweedledum
Posts
181
Registration date
Monday January 19, 2009
Status
Member
Last seen
April 2, 2009
125
Jan 23, 2009 at 04:56 AM
Jan 23, 2009 at 04:56 AM
to be honest i know very little about vba i have only done one or two projects in it, i would suggest signing up to technet forums because they have a vba forum with people who are actually experts. But i think you might have to define a range to get it to just take a few columns. It would be something like
Dim rDest As Range ' creates range variable
Set rDest = Range("G" & cnt) 'sets range variable
When i set rDest to the range cnt is a counter which updates in a loop so it isnt helpful but if you look for defining ranges on google you might find a way to set the range between two column values. Sorry i cant be of more help but its a avenue of research that might help you.
Dim rDest As Range ' creates range variable
Set rDest = Range("G" & cnt) 'sets range variable
When i set rDest to the range cnt is a counter which updates in a loop so it isnt helpful but if you look for defining ranges on google you might find a way to set the range between two column values. Sorry i cant be of more help but its a avenue of research that might help you.
